LCASE/UCASE issue when selecting DB records

[razzchic]

Hello friends,

I need your advise on a lower/upper case issue i am having on my access db.
Entries in the table appear as a mixture of cases, e.g. "john", "John", "joHn". Now, when i run my queries, it doesn't these entries as the same value even though they are. I.e. It thinks those entries are three different people.
Please Note: I have no control over the table and data in it. Sensitive info in there. I am only able to select.

1. lcase/upper functions cannot be applied on my sql statements, as i am simply selecting (not updating). So if i use lcase it simply selects only the entries in lowercase.
Or do you know of any other way?
2. I have put ">" (Without Quotes) in to the Format Option, under the General Tab when viewing the Table in Table Design View.

Still no luck.

Do you have any more suggestions on how to grab this data accurately?

 

[Golom]

Ordinarily SQL is case-insensitive on things like this. Can you post your SQL so that we can see what you are doing?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[razzchic]

Hi Golom Thanks.

I have entries like "john", JoHN", "John".
There are 11 total john entries in the db (irrespective of cases).

1. I run this:
SELECT dbo_wc_event.*
FROM dbo_wc_event
WHERE (((dbo_wc_event.ownerEmail) Like "john") AND ((dbo_wc_event.date)>#1/1/2006#));

I get 3 records returned.

2. I run this:
SELECT dbo_wc_event.*
FROM dbo_wc_event
WHERE (((dbo_wc_event.ownerEmail) Like "John") AND ((dbo_wc_event.date)>#1/1/2006#));

I get 8 records returned.

 

[razzchic]

sorry i take that back.
i only have entries like "john" and "John". 11 in total.

Any ideas?

 

[dhookom]

There is no reason to use "Like" without a wild card character. You might as well use:

  ((dbo_wc_event.ownerEmail) = "John")

It looks like your tables might be SQL Server or some other database where queries are case sensitive. If this is the case, use:

  UCase(dbo_wc_event.ownerEmail) = Ucase("John")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[razzchic]

hi dhookom,THat query works!!
THey are sybase tables.

I have other queries in the db. Can you take a look at this also? This is the major query.

SELECT DISTINCT qryNYSPerson.ownerEmail, IIf([LastName],[FirstName] & " " & [LastName],qryNYSPerson.ownerEmail) AS Name, qryNYSPerson.Vacation_Entitlement, qryNYSPerson.Manager, qryNYSPerson.EmpType, IIf([Type]="Emp",1,IIf([Type]="TCS",2,IIf([Type]="Galatea",3,IIf([Type]="Kne",4,IIf([Type]="S3",5))))) AS EmpTypeOrder, IIf([EmpType]="Employee","Emp",IIf((([EmpType]="TCSoff") Or ([EmpType]="TCSon")),"TCS",[EmpType])) AS Type, qryNYSPerson.Exclude, IIf([LastName],[LastName],qryNYSPerson.ownerEmail) AS Sort, IIf([Manager],[Manager],"zzzzz") AS SortMan, IIf([Manager],[Manager],"(To Be Referenced)") AS BlankMan, qryNYSPerson.fcal_id, StrConv([dbo_wc_event].[owneremail],2) AS email
FROM qryNYSPerson INNER JOIN dbo_wc_event ON qryNYSPerson.ownerEmail = dbo_wc_event.ownerEmail;

I tried applying your UCase function on the join field and query takes eternity to run.

 

[dhookom]

First, I would suggest you use pass-through queries where ever possible. This IIf() is confusing:

IIf([LastName],[FirstName] & " " & [LastName],qryNYSPerson.ownerEmail) AS Name,

since the first argument of IIf() is usually a true/false expression. I would also avoid naming anything "Name" as your alias states.

I also hate IIf()s nested more than 1 deep. You should have a lookup table that matches Type to a number. If you can't do that, the Switch() function would be easier to maintain.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]