Quotes in Where Clause of Search Form

[carriey]

I hope this is an easy question. I have a Search Form with a very long SQL
statement which works perfectly as long as I have the following:

strWhere = "WHERE"
strOrder = "ORDER BY Tbl_MAIN.[Field_Location];"

However, I only want my List to return those records for a specific province
and I can't seem to get it to work. I have tried the following (and also
tried them with square brackets around Province) but I think that I am
misunderstanding how to use the quotes.

strWhere = "WHERE(((Tbl_Main.Province)="Alberta"));"
strWhere = "WHERE(((Tbl_Main.Province)='Alberta'));"

Could anyone tell me what I'm doing wrong? Thanks so much!

 

[Stefan Hoffmann]

hi,

strWhere = "WHERE(((Tbl_Main.Province)='Alberta'));"

The single quotes ' are correct. But when you apply also the mentioned
strOrder, which is the last part of a SQL statement, then you'll get;

"SELECT ..
FROM ..
WHERE(((Tbl_Main.Province)='Alberta'));
ORDER BY Tbl_MAIN.[Field_Location];"

This is one semi-colon too much.


mfG
--> stefan <--

 

[Dale Fye]

As Stefan says, you can only have one ; to end the query. Also, when I'm
building my SQL in code, I leave out all of the extraneous parenthesis. All
they accomplish is making the code harder to read.

Your could should look like:

strWhere = "WHERE [Tbl_Main].[Province]='Alberta' "

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.