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:
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.
Any help is appreciated!
Code:
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 SubI 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.
Code:
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 SubAny help is appreciated!
