|
Subject:
|
SQL Statements
|
|
Posted By:
|
marmer
|
Post Date:
|
11/11/2003 6:02:33 PM
|
How do you word the WHERE portion of a SQL statement to omit records. For instance I want to omit records where the records contain a Name entry matching the password. I tried
strSQL = "SELECT * FROM Gift WHERE Name NOT 'Password' ORDER BY Name"
and it does not work.
Everything I find of for LIKE or =
Is it that Name is one of the reserved phrases?
|
|
Reply By:
|
marmer
|
Reply Date:
|
11/11/2003 6:24:57 PM
|
Well can I reply to my own topic. Just when I get frustrated and ask for help I find the answer. In case anyone else is looking for help with this, I did change the field name to Person and then remembered that I couldn't just use 'Password' in the statement or it would look for a person named Password. This is the string that works:
strSQL = "SELECT * FROM Gift WHERE Person NOT LIKE '" & Password & "' ORDER BY Person "
|
|
Reply By:
|
rodmcleay
|
Reply Date:
|
11/11/2003 8:30:50 PM
|
Marmer, while your solution will work it is not the intended purpose of the like statement.
strSQL = "SELECT * FROM Gift WHERE Person <> '" & Password & "' ORDER BY Person "
would be a better solution.
Like and not like are better for finding matches when the entire string is not known
eg Person like '%son'
would return johnson, jackson, gibson as % is a none or many wildcard.
I do not know of the performance effects of using like/not like instead of =/<> but I'm sure there will be some.
It would be better to use <> if others will be reading your code.
======================================
They say, best men are moulded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
|
|
Reply By:
|
marmer
|
Reply Date:
|
11/13/2003 12:42:36 AM
|
Thanks! For some reason, I didn't make the transfer. I use that to check to see that a form field has information, but have trouble thinking of it as a word comparison instead of a number comparison. I've used your string and it works fine. This is just for a family Christmas Wish List that I will make available for my immediate family, but I still need to form the habit of doing it correctly.
|
