Try to us my SQL knowledge in selecting a group of data contain a selected group of Chcek Numbers. I have a text box on a form that a user can enter in chcek numbers example:

10023, 10045, 10089


I am try to have my query look at this field and only pull thoe 3 numbers


SQL
----
Select * from q_Table1 where checknumber in ('10023','10045','10089')

But how do I make Access Query look at the form field?

thanks
steve

It should be something like:

Forms![YourFormName]![YourControlName]

RDH

In the check number criteria if type

IN(10016,10034,10054) IT WORKS!

but if I use the form

IN(Forms![Form1]![CheckLookup])

it says that is to complicated of a query - No Workie.

ideas?
TIA
steve

It works if I just have one value in the Form Textbox - but when I put in two seperarted by column - it breaks

thanks
steve

I don't think that what you are trying to do will work as Access will see the entry in the textbox you are referencing as one singe criteria entry instead of multiple entries seperated by a comma ...
I have created criteria statements using the IN clause .. but the SQL was created dynamically using VBA code and the IN clause was created programically in the code.

RDH

Can you pass that along (programically code) - and I'll give that a shot.

thanks
steve

I don't know if this will help you ... I had a hard time locating the application where I used the code.
Here is the relevent code that created the dynamic IN clause ...


InvoiceNo is a text datatype in the database.
This allowed the user to enter multple entries seperarted by a comma in a txtbox in a form and the code converted the entry(s) and inserted them into the WHERE of the SQL in the vba code.


RDH

So would the from code pulling the query would it be

"select * from InvoiceTBL " & strWhere

steve

Yes .. that should work ..

RDH

I have tried but

Just to say select * from ColorTBL and have it display what would I need to do


Dim stDocName As String
Dim WorkBase As Database
Dim WorkRS1 As Recordset
Dim sSQL As String

Set WorkBase = CurrentDb

sSQL = “Select * from ColorTBL”

Set WorkRS1 = WorkBase.OpenRecordset(sSQL)
WorkRS1 = WorkBase.Execute(sSQL)



*****************

thanks
steve

You can not use the Execute method to open a Select Query ...
It only works with Action queries such as Update and Append queries ....

What is the query to be use for ???
The answer to this may help with a possible soultion ...

RDH

I have a form that I want to have the user enter in a number of invoice number seperated by commas, then have then click a button to open a query.

I can build the query except when I put the IN statement in the criteria (looking at the form textbox - it does not work when there is more than one value in the textbox.

thanks for the assistance
steve.

Why don't you display query results in a form ????
You could then create the SQL as you are attempting to do and assign it to the form's recordsource when the form is being opened.

RDH