How to get an exact match

Forum|Forum|17 years ago
December 5, 2008
3 replies
853 views

How do you get a SELECT statement to look for an exact match when comparing strings ?

For example, SELECT name FROM table WHERE name="Jim"

returns records even if the name field is "JIM". I need an exact match. im using MS Access for database

Getting started

This topic has been closed for replies.

Correct answer JR__Bob__Dobbs-qSBHQ2

MS Access has a StrComp function that might help you do this.

http://support.microsoft.com/kb/209674
http://support.microsoft.com/kb/244693

D

Dan_Bracuk

Inspiring

Are you able to switch dbs? Access might be the only one that is not case sensitive.

D

DioPallyAuthor

New Participant

Unfortunatly, no. I must use their existing database. I checked and am surprised Access doesnt have a field type of binary or something simular.

So there is no way of doing this ?

J

JR__Bob__Dobbs-qSBHQ2Correct answer

Inspiring

MS Access has a StrComp function that might help you do this.

http://support.microsoft.com/kb/209674
http://support.microsoft.com/kb/244693

D

DioPallyAuthor

New Participant

I need the strings to match exactly. The fields i am comparing to is a mixture of upper and lower case characters and I cannot change the case. I need Jim to be different from JIM to be different from JiM.

Changing everything to upper case is not an exact match. The fields is a ID field generated using random upper and lower case letters and number 0-9. an example of a value in the field is:
H10h6FFSJQzjIKRi57ZWFMNkPaq8qsyGRdjCeAxM0yW1t3Ui2W

and you see why i cannot use UCase to change the case of the string.

I need an exact match

D

Daverms

Inspiring

Your select statement should be,

SELECT name FROM table where upper(name) = "#TRIM(UCASE(form.NAME))#"