Ignore Case In SQL Select Question, Help...

[Guest]

I have a table with fields of names which may or may not be all UPPER CASE or
LOWER CASE names. example " KERRY S. WILSON " could be "KERRY S. WILSON" or
"Kerry S. Wilson" or "Kerry s. Wilson". Is there a way to let a user enter
"Kerry S. Wilson" in a text entry box, then let Access query a table, search
a field and find all records with Kerry S. Wilson, regardless of case
sensitivity?

Thanks in Advance.

 

[Brendan Reynolds]

Jet SQL is always case-insensitive, you don't need to do anything to get
this behaviour in SQL. For example, the following query will return records
where the field 'TheText' contains 'one', 'ONE', or 'One' ...

SELECT TheTable.TheText
FROM TheTable
WHERE (((TheTable.TheText)="one"));

In code, you can use the UCase (or LCase) function ...

If UCase$(SomeVariable) = UCase$(SomeOtherVariable)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Lynn Trapp]

Access isn't case sensitive, so you should be able to enter the name in
either case and/or any combination thereof.

 

[Guest]

Lynn & Brendan,
Thanks for the response. I guess I'm doing something totally wrong because
what I'm attempting to do does not get results. I have an Unbound Text field
on a form, I've called it Text_O_Driver. This field has no special
treatments: no input mask, no default value, etc. The user enters a value,
example: Kerry Smith. This form has a query source named Fr_Case_DB2U_Srtd,
which holds multiple linked mainframe DB2 tables (one table named
TST_FR_OTHER_DRIVERS - which has a field in it named DRIVER_NME). Therefore
ALL RECORDS of this query contain a field named DRIVER_NME and a few of these
records have the value of KERRY SMITH. So if user enters Kerry Smith, upon
this form field changing value (AFTER UPDATE), access executes a macro called
By_O_Driver. The code in By_O_Driver is below:

OpenForm
FormName = Fr_Case_U
View = Form
Where Condition = [Fr_Case_DB2U_Srtd]![TST_FR_OTHER_DRIVERS.DRIVER_NME] =
[Forms]![Fr_Case_O_Drivers]![Text_O_Driver]
Window Mode = Normal

so form field = Kelly Smith and there are records with DRIVER_NME field
containing KELLY SMITH, but resulting display of form Fr_Case_U displays
blank record. If I supply KELLY SMITH on the input form, the query works and
finds all records, and displays form Fr_Case_U. I'd like the user to be able
to find any occurrance, regardless of the Case entry of the name. Any
suggestions.

Thanks,

Robert Nusz, Dept Public Safety, Ok


Macro

 

[Rick Brandt]

Lynn & Brendan,
Thanks for the response. I guess I'm doing something totally wrong because
what I'm attempting to do does not get results. I have an Unbound Text field
on a form, I've called it Text_O_Driver. This field has no special
treatments: no input mask, no default value, etc. The user enters a value,
example: Kerry Smith. This form has a query source named Fr_Case_DB2U_Srtd,
which holds multiple linked mainframe DB2 tables...[SNIP]

There you go. Linked to DB2 tables the query WILL be case sensitive. You can
use....

Where UCase([Field]) = UCase(YourSearchString)

It won't be very efficient though.

 

[Guest]

To all that assisted in support of this project, THANK YOU.

My problem has been resolved.

I was executing a MACRO after update to a field on a form. That macro
needed to be coded as:
------------------------------------------------------
OpenForm
Form Name = Fr_CR_U
Filter Name = nul
UCase([Fr_Case_DB2u_Srtd]![TYPIST_INIT_TXT])=UCase([Forms]![Fr_Search_By_Typist]![Text9_Typist_Initials])
Data Mode = Edit
Window Mode = Normal
------------------------------------------------------

By adding UCase on both values as well as fully enclosing them into ( and )
it fully qualified the comparisons to accept the selection. Now, if user
enters a value of ben for a value (say name) and the record contained name
as BEN, the query will select ALL records.

Thanks Again folks.
Just wanted to pass along the solution.
Again, this was Microsoft Access 2003 Professional Front-End to IBM
Mainframe DB2 Backend data.

Robert Nusz
DPS, State of Oklahoma