Using string in IN statement

Kevin Elliott

New member
Local time
Today, 20:32
Joined
Mar 6, 2013
Messages
3
Hello everyone,

I have a list box which allows return of multiple values. I have some VBA which builds a string of all the values returned, and has put a comma between each value selected.

The values at the moment are just numbers, so the string looks like

307, 328, 400, 420, 502

I have a query with an IN statement in the criteria field. If I hard code these values (with parentheses) it works fine, but if I try to pass in the value of a text box which contains the string of values, it doesn't return any data at all if I have two or more numbers in the string. One number on its own is fine.

Any help would be very much welcomed! :banghead:

Cheers,

Kevin.
 
Hello Kevin, Welcome to AWF.. :)

Could you show us the Query you currently have? If it is something like..
Code:
SELECT * FROM someTable
WHERE someField In Forms!formName!textBoxName
Would this not work?
Code:
SELECT * FROM someTable
WHERE someField In [COLOR=Red][B]([/B][/COLOR]Forms!formName!textBoxName[B][COLOR=Red])[/COLOR][/B]
 
Think it needs to be like this

"SELECT * FROM someTable
WHERE someField In (" & Forms!formName!textBoxName & ")"
 
Nope, you can't create an IN clause that way. You would have to build the SQL using VBA and modify the query using a QueryDef object before you can run it.
 
Yup Bob's right.. It does not work that way.. :o Just tested it.. Creating QueryDefs is not really that hard.. You can look at Microsoft Website on creating a simple one (the last bit of coding)..
 
Yup Bob's right.. It does not work that way.. :o Just tested it.. Creating QueryDefs is not really that hard.. You can look at Microsoft Website on creating a simple one (the last bit of coding)..

I like using the SQL Tools that Access MVP Armen Stein provides for free here:
http://www.jstreettech.com/files/basJStreetSQLTools.zip

Copy the contents of that text file into a new module and save it as basSQLTools for example. Then when you need to do the replacement it becomes extremely easy to just replace the Where clause:

Code:
Dim qdf As DAO.QueryDef
Dim strWhere As String
Dim varItem As Variant
 
For Each varItem In Me.ListBoxNameHere.ItemsSelected
    strWhere = strWhere & Me.ListBoxNameHere.ItemData(varItem) & ","
Next
 
If Right(strWhere, 1) = "," Then
   strWhere = Left(strWhere, Len(strWhere)-1)
End If
 
strWhere = "FieldNameHere In(" & strWhere & ")"
 
Set qdf = CurrentDb.QueryDefs("yourQueryNameHere")
 
qdf.SQL = ReplaceWhereClause(qdf.SQL, strWhere)
qdf.Close
 
Set qdf = Nothing
 
Much appreciated guys. Trying the querydefs solution now, will let you know how I get on.

Thanks, Kevin.

Edit: Got this working, so thanks again. I just needed to add the WHERE statement to the start of the sql string.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom