Array as query parameter 1

[Seeff]

Hi....I have an array populated with values. I would like to use these values as criteria for a query. How do I do this?

eg. My table (300 records)has a Code field with 20 different unique values. The user has picked from a listbox 3 different values (eg. 5,8 and 15). I have them in an array. Now, what is the code I use to select all the records where code is 5 or 8 or 15???

Thanks...L

 

[GHolden]

Don't know if this will help. Here is an example of building an sql string from selected items in a list box.

Dim varItem
Dim strSQL As String
Dim strSelection As String

'First part of SQL string
strSQL = "SELECT * FROM tblName "

'Loop through all selected items in listbox
For Each varItem In lstList.ItemsSelected
'if first item
If strSelection = vbNullString Then
'add WHERE table name, field name and data from column one in listbox
strSelection = strSelection & " WHERE tblName.Name = '" & lstList.Column(1, varItem) & "'"
Else
'add AND table name, field name and data from column one in listbox
strSelection = strSelection & " AND tblName.Name = '" & lstList.Column(1, varItem) & "'"
End If
Next varItem

MsgBox strSQL & strSelection

There are two ways to write error-free programs; only the third one works.

 

[Seeff]

ok...that is great, I have the correct SQL statement. Now, how and where do I run it to be able to view the resulting recordset.

Thanks

 

[GHolden]

It kind of depends on how you want to view it and what you want to do with it..??

There are two ways to write error-free programs; only the third one works.

 

[GHolden]

BTW

strSelection = strSelection & " AND tblName.Name = '" & lstList.Column(1, varItem) & "'"

should be

strSelection = strSelection & " OR tblName.Name = '" & lstList.Column(1, varItem) & "'"

There are two ways to write error-free programs; only the third one works.

 

[Seeff]

Yes, I picked up on the OR instead of AND.

I want to, at this stage perform the SQL on the table, simply like an ordinary query with a datasheet view.

 

[GHolden]

If you just want to view the results, probably the easiest way would be to have another listbox and use the SQL to set the rowsource

eg.

lstResults.RowSource = strSQL & strSelection

There are two ways to write error-free programs; only the third one works.

 

[Seeff]

OK!!

That is it, works perectly. Thanks for your Gholden help.