Case sensitive Query

[MICKI0220]

I run a query on ODBC connected databases within Access 2000 mode....it says data mismatch if I use a different case then what is in the table....How do I make it not be sensitive to the case or make it recognize upper case no matter what case is put into the criteria box. This is the example....a report is run based on the query. The filtering criteria is the customer order number which is something like 08/09/11K-1/5. If i put in a lower k it errors out with a datatype mismatch.

I hope this is an easy one for someone

Thanks

Micki

 

[dhookom]

You can use the UCase() function in the WHERE clause like:

WHERE UCase([CustOrdNum]) = UCase(Forms!frmA!txtCustOrdNumSearch)

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

It uses a pop up from the query instead of a form....In the query criteria there is like [custormer number] so a popup comes up. How would that impact the where clause?

 

[dhookom]

faq701-6763

If you really want to use a pop up, then substitute your parameter for the much better reference to a control on a form.

If you can't figure this out, please reply back with the full SQL view of your query. Also, does your field in the database always have uppercase values or is it a mix?

Duane
Hook'D on Access
MS Access MVP