Multiple Criteria String Trouble

[McAccess]

Hi all. Amateur here and in need of a quick fix. I have used the code provided by Allen Browne http://allenbrowne.com/ser-50.html to use a multi select box to filter a report via criteria string compilation. This works great by itself. I have it set up to compile select cities into a criteria string. See below:

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
    'Purpose:  Open the report filtered to the items selected in the list box.
    'Author:   Allen J Browne, 2004.   [URL="http://allenbrowne.com/"]http://allenbrowne.com[/URL]
    Dim varItem As Variant      'Selected items
    Dim strWhere As String      'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of report to open.
    
    strDelim = """"            'Delimiter appropriate to field type. See note 1.
    strDoc = "Sales Report"
    'Loop through the ItemsSelected in the list box.
    With Me.ListCity
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from selected column.
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                'Build up the description from the text in the visible column. See note 2.
                strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
            End If
        Next
    End With
    
       
    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
        strWhere = "[ApartmentCity] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "Cities: " & Left$(strDescrip, lngLen)
        End If
    End If
    
    'Report will not filter if open, so close it. For Access 97, see note 3.
    If CurrentProject.AllReports(strDoc).IsLoaded Then
        DoCmd.Close acReport, strDoc
    End If
    
    'Omit the last argument for Access 2000 and earlier. See note 4.
    DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
    DoCmd.Close acForm, "SalesFilter", acSaveYes
        
    
Exit_Handler:
    Exit Sub
Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
    End If
    Resume Exit_Handler
End Sub

I now need to add an optional Date criteria to the string, but for the life of me, can't pull it off. This simple date criteria would be great if I could figure out how to add it to the string above? The [StartDate] field comes from an unbound box on the same form as the multiselect data used above. The [SalesDate] field is in the query that the Report I am trying to filter is based on. The Problems definitely relate to the date data not playing nice with the string data compiled above.

Private Sub DateFilter()
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.StartDate) Then
        strWhere = strWhere & "([SalesDate] >= " & Format(Me.StartDate, conJetDate) & ") AND "
    End If
End Sub

Any help is appreciated!

 

[Galaxiom]

conJetDate = "\#mm/dd/yyyy\#"

However I can see how McAccess got the idea of escaping the forward slashes. Makes no sense to me.

http://www.mvps.org/access/datetime/date0005.htm

Perhaps someone could explain because this makes even less sense:
Global Const JetDateTimeFmt = "\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l"

This works but must include that weird bit on the end if the forward slashes are escaped. Looks a lot like ofcuscated Null to me and what is with the three semicolons?

 

[McAccess]

conJetDate = "\#mm/dd/yyyy\#"

That was the fix I needed. I also had to tweak the following a bit:

If Not IsNull(Me.StartDate) Then
        strWhere = strWhere & " AND " & "([Sale Date] >= " & Format(Me.StartDate, conJetDate) & ")"
    End If

Man! You saved the day my friend. Thanks a lot!

 

[Galaxiom]

Perhaps someone could explain because this makes even less sense:
Global Const JetDateTimeFmt = "\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l"

Answered here:
http://www.access-programmers.co.uk/forums/showthread.php?p=905644#post905644