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 ***
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 ***