Using the In (..) statement in a query

[docliv]

Hi,
I'm new at access and have hit a wall. I have a query where I use the.. In(a05,a02).. in a field to find all records that have "a05" and "a02" in that field. I want to be able to change the criteria that is in prints() on the fly using cut and paste or some kind of prompt. When I use a prompt and paste in the date (for example "a05"."a02&quot, it doesn't work, when I try to point to a field in another table that has this data, it doesn't work either. I tried writing a macro that would let me paste this data, but I can seem to move around in a query that is opened in design view rather than datasheet view...what kind of deal is this. This could all be avoided it I could just record keystokes, but I don't think I have that option. Any help anyone could give when be appreciated.. the application I am working on needs to be able to use this In(..) statement and fill in the print() based on changing data.
Thanks in advance for any response...kinda desperate and I have wasted a lot of time playing with this.
Thanks again.
Terry

 

[KenReay]

Hi

To use a table to hold your list:

SELECT .... etc WHERE key IN (SELECT myKey FROM MyTable); Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[docliv]

Thanks KenReay for the response.. Can you tell me what the exact syntax would be for this statement in a criteria field of the query, if for instance my list that I wanted to use inside the prints() of the In() statement is stored in a table called Mytable in a field named List. thanks so very much for your help.
Terry

 

[KenReay]

Hi

Don't understand your question?

the table MyTable would have n rows so:

A02
A05
...etc

is that what you want to know? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[docliv]

Hi Ken,
Thanks again so very much for trying to help me, I apologize for not being very clear.. it comes from me just trying to get into this "access" database program..I had used Paradox in the past (and thought I was pretty good at it...maybe not :>)..
At any rate, the table that I want to use to fill in a criteria field with the In() statement would have a field that contained "A05,A06,B09,B10" all on one row in one field, with no quotation marks of course. This field would vary from, for example, the 4 items listed to maybe up to 10 to 12 items depending on another query. I would like to have a criteria statement in a query field that says
In(information from table)which in the example I used would be In(A05,A06,B09,B10) and would find all records containing this data. I hope this explains what I am trying to do a little better, and again thanks for your help and patience with a newbie.
Terry

 

[KenReay]

Hi

OK to do this, I think you will have to build the SQL string in code.

Dim strSQL as String

,,,code to read the table with the string in it

strSQL = "SELECT .... etc WHERE key IN (" & Rs!strIN & ""

OK? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[docliv]

Ken,
Thanks again so much for your help...what you wrote may very well work, but it is way above my abilities right now. I didn't want to get into VB code or procedures to do this. I'm not sure where to put the code or call for it. I just wanted to be able to fill out the criteria field with data that could change. What gets me is that I can manually cut and paste the data and everything works, but when using prompts, it interpets the data as a whole instead of a string. I have come up with 36 different combinations of data...I think I am going to make 36 different queries and call on them based on criteria the uses puts in. I know it's not a very streamline solution, but it will work..I think..
Again, thanks for your input.

Terry