Using IN statement in Query Criteria Field

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

I'm using an IN Statement in a query criteria field that
referenced a text field in a form. ie. IN (forms!frmABC!
text11). The problem I encounter is the query will only
work if I only have one piece of data in the text field.
If I try to put more that one peice of data in the text
field it will not work. I tried seperating the data with a
comma but it still does not work. I have a feeling there
is a problem with the separator. Any suggestions would be
most helpful.
 
You cannot do it that way. You can only use a parameter to substitute a
single literal value in a query. So, if you type in text11:

"One", "Two", "Three"

... the query acts as though you entered:

WHERE MyField IN ('"One", "Two", "Three"')

The way to do this correctly is to either use several separate text boxes
that you reference in the IN clause or to build the predicate dynamically in
code and use it as a filter for the form or report bound to the query that
has no IN clause.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top