SQL Where clause (2000 SP3)

Topic

New Reply

Can someone tell me what is wrong with this picture, please?
“SELECT LOC, Funding From qryTest WHERE LOC ='” & strLOC “‘ And LOC Not Like ‘US *’;”

The query works in Access, but when I try to run it from code, I can’t get it to exclude the ‘US *’ (the field actually contains a country name, and in the case of the US it is in the form ‘US – XX’ where XX is the 2-letter state code.

I am sure it is something simple, but I can’t think of what it might be!

Thanks,

Kiwi44

Reply | Quote

Replies

There is an ampersand missing after strLOC:

“SELECT LOC, Funding From qryTest WHERE LOC ='” & strLOC & “‘ And LOC Not Like ‘US *’;”

Reply | Quote

Thanks – but that doesn’t work either. The statement still selects all records in qryTest. I have also tried taking it one step back to the SQL statement that leads to population of strLOC, and the “Not LIke ‘US *'” doesn’t work either.

Kiwi44

Reply | Quote

I don’t think there is a way we can help you further without seeing the database. If you like, you can post a stripped down version of the database:

• Make a copy of the database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• Attach the zip file to a reply.
  [/list]

Reply | Quote

Thanks for the suggestion. In another post I discovered that simply cutting and pasting macros into VB code does not work (guilty as charged!), so I need to spend a little time cleaning up the code I have. I will come back if there are still problems after I have done that.

kiwi44

Reply | Quote

Thanks for the suggestion. In another post I discovered that simply cutting and pasting macros into VB code does not work (guilty as charged!), so I need to spend a little time cleaning up the code I have. I will come back if there are still problems after I have done that.

kiwi44

Reply | Quote

I don’t think there is a way we can help you further without seeing the database. If you like, you can post a stripped down version of the database:

• Make a copy of the database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• Attach the zip file to a reply.
  [/list]

Reply | Quote

Thanks – but that doesn’t work either. The statement still selects all records in qryTest. I have also tried taking it one step back to the SQL statement that leads to population of strLOC, and the “Not LIke ‘US *'” doesn’t work either.

Kiwi44

Reply | Quote

There is an ampersand missing after strLOC:

“SELECT LOC, Funding From qryTest WHERE LOC ='” & strLOC & “‘ And LOC Not Like ‘US *’;”

Reply | Quote