Concatenate criteria from a form with a list box

[jokeascool]

Hello all,


I am having problems trying to prepare criteria from a multi select
list box on a form.


I have written a loop to catch all of the criteria, however when I go
to open the query to check the results it asks me to enter the
parameters.


The form pulls criterea for 4 columns 3 of which are single criteria
and the last being from the list box.


Here is the code I am using.


Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim intI As Integer


Set frm = Form!ScheduleA_Generator_frm
Set ctl = frm!House_Code
strSQL = "Select * from House_Code"


intI = 0
For Each varItem In ctl.ItemsSelected
intI = intI + 1
If intI = 1 Then
strSQL = strSQL & " WHERE [House_Code] = " & ctl.ItemData(varItem)
Else
strSQL = strSQL & " OR [House]= " & ctl.ItemData(varItem)
End If
Next varItem


Any help would be appreciated. I am new to vba so if you could make it

as simple as possible I would appreciate it!


Thanks in advance


Joe

 

[Guest]

Your code is adding as many "where" clauses as items selected, try this instead

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strList as string
Dim intI As Integer


Set frm = Form!ScheduleA_Generator_frm
Set ctl = frm!House_Code
strSQL = "Select * from House_Code " (add a space in this line at the end)
strSQL = "Where [House_Code] in ("

strList=""
For Each varItem In ctl.ItemsSelected
' Use "'" if your values are strings, discard if they are numbers
if len(strList)>0 then
strList=strList & ","
end if
strList= strList & "'" & ctl.ItemData(varItem) & "'"
Next varItem

strSQL = strSQL & strList & ")"

strList should create a list of values like 'CODE1','CODE2',..., etc.

 

[Van T. Dinh]

See The Access Web article:

http://www.mvps.org/access/forms/frm0007.htm