parameter query with IN operator

[mcalex]

hi all

I'm trying to create a parameter query where the user supplies a list of years, and the query uses the IN operator with the supplied criteria. eg if the user wants data from last year, 2007 and 2001, they type the following in the parameter dialog:
(2001, 2007, 2009)

Access tells me that the expression is typed incorrectly or is too complex to be evaluated and then suggests I simplify the expression by assigning parts of the expression to variables. I have tried with brackets around the whole parameter as above, no brackets, brackets around each item - and just to make sure all straws were clutched - quotes around each item.

The query design grid has: In ([Years_Parm]) in the Criteria bit.

I can get it to work with 'Like [Years_Parm]' when I input 200*.
I can get it to work with 'Between [Year_Parm1] And [Year_Parm2]' when I input a start and end year.
I just can't get it to work with 'In ([Years_Parm])'

I want the In operator so I don't have to deal with data from years I don't need (ie 2002-2006 & 2008 in the above)

Any suggestions?

tia,
mcalex

 

[georgedwilkinson]

What data type is your year field?

Have you tried inputting:
("2001", "2007", "2009")?

 

[mcalex]

Hi George

The data type is Variant (Integer).

I have a date field that I am extracting the year from with the Year() function to get the field in question.

I have tried quotes. First I put quotes around each item without success. Then I tried surrounding the entire (quoted) list with brackets. This also didn't work.

 

[MStef]

Look at "DemoInParameterA2000.mdb" (attachment, zip).
Look at Table1, Query1, Query3.
I think it is what you need.

 

[mcalex]

Hi MStef

not quite.

I want the year to be a parameterized query. Ie so the user can choose which years to run the query on.

 

[georgedwilkinson]

What would the EXACT Sql look like when the query is finished? Paste that into a query window and experiment with various formats of your condition. If you are comparing dates (which I don't think you are), the delimiter is a #. For text (which it looks like you're using), the delimiter is ', though " is permissible in Access. If numeric (which I don't think you're using) there is no delimiter.

If you can't figure it out, post the EXACT Sql that is run.

 

[Brianwarnock]

You can not parameterize the IN function without resorting to VBA, there was a recent thread with a link.
I will check it out.

Brian

 

[Banana]

Brian is correct.

It's a common error by many people new to SQL to think that IN(@Param) means "parse whatever is in the parameter". It doesn't. It is more appropriate to think of "field IN(1,2, 3)" as a compile-time shortcut for this expression: "field = 1 OR field = 2 OR field = 3"

You could use VBA to change the SQL at runtime with the actual content of the parameters:

CurrentDb.Execute "SELECT ... WHERE field IN (" & aListOfCommaDelimitedStrings & ");", dbFailOnError

Alternatively, if the table is big and/or performance is important, use a temporary table to contain the parameters and join the tables on that.

 

[Brianwarnock]

This was the thread I mentioned .
http://www.access-programmers.co.uk/forums/showthread.php?t=186404

The next to last post has the link.
Brian

 

[mcalex]

ahh, bummer.

ok, thanks for that. I was hoping to get away with bog-std access, and only use the inbuilt parameter dialog. Didn't even want a form. Oh, well - looks like it's <Alt-F11> time again