Run Time Error

[JSalle0826]

Hello all,

I am getting a Run Time Error for some code in which I am trying to Run a Query and use a multi-select list box as criteria for the query. I built the Query, and created a form with an unbound control with the list box and the items I want. I created an "OK" button on the form in which I want the query to provide me with email addresses for the departments in which they select on the form.

Here is my code:

Private Sub OK_Click()
Dim Q As QueryDef, DB As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant
' Build a list of the selections.
Set ctl = Me![List0]
For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm
If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If
' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("EmployeeQuery2")
Q.SQL = "SELECT Employee.Email, Departments.[Department ID], * FROM Employee INNER JOIN (Departments INNER JOIN [Department Details] ON Departments.[Department ID] = [Department Details].[Department ID]) ON Employee.[Employee ID] = [Department Details].[Employee ID] WHERE Departments.[Department ID]= In(" & Criteria & ");"
Q.Close
' Run the query.
DoCmd.OpenQuery "EmployeeQuery2"
End Sub

 

[jdraw]

Put a debug.print criteria before your

' Run the query.

This will print the value of criteria in the immediate window; record it and post back what you find.

 

[JSalle0826]

I tried and it won't work as I keep getting the Run-Time Error. It states:

Run-time error '3075':

Syntax error (missing operator) in query expression 'Departments.[Department ID]=In("7")'.

In the error message the "7" represents one of my Department ID's that I had chosen in the multi-select list box. If I select multiple entries, I get the same message, however the number changes (depending on the department selected) for example if I select two departments, the error message says:


Syntax error (missing operator) in query expression 'Departments.[Department ID]=In("7", "14")'.

 

[JSalle0826]

I moved the Debug.Print Criteria above 'Modify the Query line and the Immediate window returned "7", "14".

 

[jdraw]

2 things

you don't want = and In
In this case you want In
however, I think your ID field is numeric, not text, so you do not need the " quotes

see http://www.techonthenet.com/sql/in.php

 

[JSalle0826]

Yes, jdraw! That did it, thank you so much!

 

[jdraw]

You are welcome - glad to help.