Building a SQL expression in VBA

G

Garry Douglas

The following is an extract from the WHERE element of a SQL expression
behind a select query which, in this instance, returns a record whenever the
Genre field contains the word "blues".

WHERE (((Products.Genre) Like "*blues*"))

How should I format the contents of a text box (txtGenre) on a form in a
line of VBA so that it I can build up the SQL property of a query in code to
achieve the same result?

I have tried using the following code.

strWHERE = strWHERE & " AND PRODUCTS.GENRE Like " & txtGenre

(strWHERE is a string variable containing the composite WHERE element of the
SQL expression. Depending on the other selections made by the user strWHERE
may already contain text by the time this line executes hence the joining of
the
strings)

Using this section of code the procedure completes without generating any
errors but when I attempt to open the query it prompts for a value for the
Genre field i.e. it is a parameterised query! I assume that this is due to
the absence of the quotation marks and asterisks surrounding the value of
txtGenre.

Trying to force asterisks to surround the value of the txtGenre thus:

strWHERE = strWHERE & " AND PRODUCTS.GENRE Like " & "*" & txtGenre & "*"

produces a Run Time Error 3075 - Syntax Error (missing operator in query
expression).

I can provide full details of the code if that will help but thought it
might be easier to initially just include the extracts.

Thanks for any advice.

Garry Douglas

*** Please Remove SPMOFF to Reply ***
 
J

John Spencer (MVP)

First you need to get the text delimiters in ther

strWHERE = StrWhere & " AND Products.Genre Like " & Chr(34) & txtGenre & Chr(34)

or

strWHERE = StrWhere & " AND Products.Genre Like '" & & txtGenre & "'"

Or

strWHERE = StrWhere & " AND Products.Genre Like """ & txtGenre & """"

Further if you are using datetime elements they must be delimited by # vice
quotes and datetimes should be in the US Format (mm/dd/yyyy) or in yyyy/mm/dd
format.
 
G

Garry Douglas

John Spencer (MVP) said:
First you need to get the text delimiters in ther

strWHERE = StrWhere & " AND Products.Genre Like " & Chr(34) & txtGenre &
Chr(34)

or

strWHERE = StrWhere & " AND Products.Genre Like '" & & txtGenre & "'"

Or

strWHERE = StrWhere & " AND Products.Genre Like """ & txtGenre & """"

Further if you are using datetime elements they must be delimited by #
vice
quotes and datetimes should be in the US Format (mm/dd/yyyy) or in
yyyy/mm/dd
format.

Thanks for the advice.

I realised I could do it with ANSI character codes 5 minutes after I posted.

Don't you hate it when that happens?

Garry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top