Parameter Query with 5 listboxes

dvenkat · May 13, 2002

Hi-

I am developing a query (parameter) with 5 list boxes. I am using the following code (see below) to retrieve records from the query based on the user options/ choices. However I get this error message when I run the query.

Error Message:
Run-time error '3075'
Syntax error (missing operator) in query expression '([Assy] In()) AND ([PTI2] In()) AND ([PkgCd] In()) AND ([ProdLine] In ()) AND ([CycleMonth] In())

This error message is when I select nothing. If I select something, I get the same error message but with that selection in the appropriate field - e.g. Syntax error...................AND ([PTI2] In("JD&quot

) AND ([PkgCd] In("0051&quot

)...........

In my query design view, I try to insert the following but it vanishes when the query is executed.
Expr1: Nz([PTI2])
Criteria: Like ("*&quot

Similarly I have the same thing for other list boxes except CycleMonth which is a date field. I left that blank.

But as I said, when I try to execute the query from the form, I get the error message and when I open to see the query all my expressions are gone.

CAN ANYONE HELP ME WITH THIS. I HAVE BEEN TRYING FOR 2 WEEKS BUT GOING NOWHERE. I AM NEW TO ACCESS AND ACCESS BASIC (VB). ANY HELP WILL BE GREATLY APPRECIATED.

Thanks........

----------------------------------------------------------
Here is my code:

Sub RunTest_Click()

Dim AssyDB
Dim Q

Dim Criteria1 As String
Dim Criteria2 As String
Dim Criteria3 As String
Dim Criteria4 As String
Dim Criteria5 As String

Dim ctl1 As Control
Dim ctl2 As Control
Dim ctl3 As Control
Dim ctl4 As Control
Dim ctl5 As Control

Dim Itm1 As Variant
Dim Itm2 As Variant
Dim Itm3 As Variant
Dim Itm4 As Variant
Dim Itm5 As Variant

Set ctl1 = [Forms]![frm_main]![lstassy]
Set ctl2 = [Forms]![frm_main]![lstpti2]
Set ctl3 = [Forms]![frm_main]![lstpkgcd]
Set ctl4 = [Forms]![frm_main]![lstprodline]
Set ctl5 = [Forms]![frm_main]![lstcycmon]

For Each Itm1 In ctl1.ItemsSelected
If Len(Criteria1) = 0 Then
Criteria1 = Chr(34) & ctl1.ItemData(Itm1) & Chr(34)
Else
Criteria1 = Criteria1 & "," & Chr(34) & ctl1.ItemData(Itm1) & Chr(34)
End If
Next Itm1

For Each Itm2 In ctl2.ItemsSelected
If Len(Criteria2) = 0 Then
Criteria2 = Chr(34) & ctl2.ItemData(Itm2) & Chr(34)
Else
Criteria2 = Criteria2 & "," & Chr(34) & ctl2.ItemData(Itm2) & Chr(34)
End If
Next Itm2

For Each Itm3 In ctl3.ItemsSelected
If Len(Criteria3) = 0 Then
Criteria3 = Chr(34) & ctl3.ItemData(Itm3) & Chr(34)
Else
Criteria3 = Criteria3 & "," & Chr(34) & ctl3.ItemData(Itm3) & Chr(34)
End If
Next Itm3

For Each Itm4 In ctl4.ItemsSelected
If Len(Criteria4) = 0 Then
Criteria4 = Chr(34) & ctl4.ItemData(Itm4) & Chr(34)
Else
Criteria4 = Criteria4 & "," & Chr(34) & ctl4.ItemData(Itm4) & Chr(34)
End If
Next Itm4

For Each Itm5 In ctl5.ItemsSelected
If Len(Criteria5) = 0 Then
Criteria5 = Chr(34) & ctl5.ItemData(Itm5) & Chr(34)
Else
Criteria5 = Criteria5 & "," & Chr(34) & ctl5.ItemData(Itm5) & Chr(34)
End If
Next Itm5

Set AssyDB = CurrentDb()
Set Q = AssyDB.QueryDefs("qry_ddprmrsactuals&quot

'If cbodatatype.Text = "ALL" Then
If (ctl5.Selected(0) = True And ctl4.Selected(0) = True And ctl3.Selected(0) = True And ctl2.Selected(0) = True And ctl1.Selected(0) = True) Then
Q.SQL = "SELECT tbl_ddpr_mrs_actuals.Assy, tbl_ddpr_mrs_actuals.Test, tbl_ddpr_mrs_actuals.PTI2, tbl_ddpr_mrs_actuals.PkgCd, tbl_ddpr_mrs_actuals.PkgDesc, tbl_ddpr_mrs_actuals.ProdLine, tbl_ddpr_mrs_actuals.TestPlatform, tbl_ddpr_mrs_actuals.CycleMonth, tbl_ddpr_mrs_actuals.PlanMonth, tbl_ddpr_mrs_actuals.DataType, tbl_ddpr_mrs_actuals.Outs FROM tbl_ddpr_mrs_actuals WHERE (Nz([Assy]) Like(" & Criteria1 & &quot