I am building a database in Access2000. One of the features will allow you
to run a automated query by selecting multiple items from a listbox. I managed
to get this to work with strings, but get errors when I try to query a date
field. Obviously it is because I am querying a date field with a string, thus
Generating a type mismatch, and other errors.

Please review the code below, and if anyone can assist me with whatever
modification can be made to make this work with date field, it would be
much appreciated.

The only thing I could think of was to DIM the variable used to store
the date value as a "date" type, but that did not work either. Probibly
because the whole query is stored as a string.???

Here is the code behind the control, which calls the Query:
__________________________________________________ ______

Private Sub cmdMultiByDate_Click()

On Error GoTo Err_cmdMultiByDate_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM SORDERS"

'Build the IN string by looping through the listbox
For i = 0 To LstMonth.ListCount - 1
If LstMonth.Selected(i) Then
If LstMonth.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & LstMonth.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [MONTH] in (" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "QRY_MULTIBYDATE"
Set qdef = MyDB.CreateQueryDef("QRY_MULTIBYDATE", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "QRY_MULTIBYDATE", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.LstMonth.ItemsSelected
Me.LstMonth.Selected(varItem) = False
Next varItem


Exit_cmdMultiByDate_Click:
Exit Sub

Err_cmdMultiByDate_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection Required !"
Resume Exit_cmdMultiByDate_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdMultiByDate_Click
End If

End Sub
__________________________________________________ _______

Again any help would be great.

Assuming [MONTH] is the date field, you can use the # sign to delimit the dates in strIN:-

strIN = strIN & "#" & LstMonth.Column(0, i) & "#,"

Note
# works on US dates only. On non-US date systems, you will need to format the dates as m/d/yyyy for the # sign to work properly:-

strIN = strIN & "#" & Format(LstMonth.Column(0, i), "m/d/yyyy") & "#,"
.