Query criteria from listbox problem

[dannyspencer12]

Hi there. I have a problem with queries that i am creating on the fly with access. My setup is this:

A table called Order which has various fields including a field called Date Taken. The format of this is Date/Time with the format d/m/yyyy.

A form called frmMultiSelect which has a listbox called lstdates and a command button called cmdOpenQuery.

in lstdates the row source is: SELECT DISTINCT Order.[Date Taken] FROM [Order] UNION select "......ALL......" from [Order];

And in the button onClick event i have the following code (adapted from a web source):

Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_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 [Order]"
    
    'Build the IN string by looping through the listbox
    For i = 0 To lstDates.ListCount - 1
        If lstDates.Selected(i) Then
            If lstDates.Column(0, i) = "......ALL......" Then
                flgSelectAll = True
            End If
            strIN = strIN & "#" & lstDates.Column(0, i) & "#,"
        End If
     Next i
     
    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE [Date Taken]=" & 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 "qryCompanyCounties"
    Set qdef = MyDB.CreateQueryDef("qryCompanyCounties", strSQL)
    
    'Open the query, built using the IN clause to set the criteria
    DoCmd.OpenQuery "qryCompanyCounties", acViewNormal
    
    'Clear listbox selection after running query
    For Each varItem In Me.lstDates.ItemsSelected
        Me.lstDates.Selected(varItem) = False
    Next varItem
    
    
Exit_cmdOpenQuery_Click:
    Exit Sub
    
Err_cmdOpenQuery_Click:

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

End Sub

When i open the form and look at the list box the dates are there along with the ......ALL...... however the dates whose day is less than 10 are displayed with the leading 0 e.g 04/12/2005.
When i click the run query command button on one of these dates the query is opened blank. the sql code output is:

SELECT *
FROM [Order]
WHERE [Date Taken]=#04/12/2005#;

If it was changed to 4/12/2005 it works but i dont know why access is putting in this leading 0.

Please help me!! I can send the database if needed

Thanks very much

 

[gromit]

hi danny -

What is the data source for the list box? Are coming from a table or from a value list?

Have you tried printing out the strIN variable to see if it has the leading 0's there?

-g