How to pass numbers from a string using the In() 1

[GKIL67]

Hello all,

I have a text filed [Nos] on a form [CheckNos] containing a series of numbers such as "6;11;20".

I want to pass them in a query as a filter, using a button.
The query has in the respective field as criteria:
In ([Forms]![CheckNos]![Nos])

How can I use the In() to retrieve all numbers from the string?
The numbers in the string could be from 1 to 100.
Could you provide me with some help?

Thank you in advance!

 

[dhookom]

You have a few options. One is to use code to change the SQL of the querydef.

The simplest might be to try create a new column with an expression like:

Instr(";" & [Forms]![CheckNos]![Nos] & ";",";" & [YourFieldName] & ";")

Set the criteria under this column to

>0

Duane
Hook'D on Access
MS Access MVP

 

[GKIL67]

Great! I got it to work using instr(), although it was more complicated because the logic had to be applied to 5 fields and
then select only those that match the filter...
And the "," had to be a ";" in order for the query to work.

Would it be too much to ask a bit more about your comment: "to use code to change the SQL of the querydef"...
Even if I took the numbers one by one, how would I pass them using In()? Or did you mean changes like from "," to ";" ?


Thank you so much!

 

[dhookom]

The changing to the SQL property of the saved query would use DAO code like:

Dim db As DAO.Database
Dim qd as DAO.QueryDef
Dim strSQL as String
strSQL = "SELECT... " & _
    "FROM ..." & _
    "WHERE [NumberField] In (" & Me.[Nos] & ");"
Set db = CurrentDb
Set qd = db.QueryDefs("YourQueryName")
qd.SQL = strSQL
set qd = Nothing
Set db = Nothing

Duane
Hook'D on Access
MS Access MVP

 

[GKIL67]

Indeed, the Instr() is the most simple way to get it done, especially when seeking results mostly with queries.

Thank you!