Filtering Access report by dates

[ccwells]

Hi,

I am a novice to VB, using Access 2003 on a XP-SP2 platform, and I have a small database for tracking expenses, and want to be able to filter my expense report by date. I currently have a form with two list boxes, purchase and payment type, and then two date fields txtStartDate and txtEndDate. Filter works fine when no dates are chosen, problem is with my date picker - if I choose dates I get a "syntax error (missing operator)" error in my strWhere statement (I have used the debugger, but can't pinpoint the syntax error.. I have posted the script for the "Apply FIlter" command, any assistance would be appreciated. By the way, my date field in my source table us uDate, and in my report is "repDate" to avoid reserved fields.

Script:

 Private Sub cmdApplyFilter_Click() 
Dim varItem As Variant
Dim strCategory As String
Dim strPayment As String
Dim strFilter As String
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
 
 
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "repExpense") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string from lstCategory listbox
For Each varItem In Me.lstCategory.ItemsSelected
strCategory = strCategory & ",'" & Me.lstCategory.ItemData(varItem) _
& "'"
Next varItem
If Len(strCategory) = 0 Then
strCategory = "Like '*'"
Else
strCategory = Right(strCategory, Len(strCategory) - 1)
strCategory = "IN(" & strCategory & ")"
End If
' Build criteria string from lstPayment listbox
For Each varItem In Me.lstPayment.ItemsSelected
strPayment = strPayment & ",'" & Me.lstPayment.ItemData(varItem) _
& "'"
Next varItem
If Len(strPayment) = 0 Then
strPayment = "Like '*'"
Else
strPayment = Right(strPayment, Len(strPayment) - 1)
strPayment = "IN(" & strPayment & ")"
End If
' Build criteria string for date
 
strReport = "repExpense"
strField = "Date"
 
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & "<=#" & Me.txtEndDate
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & ">=#" & Me.txtStartDate
Else 'Both start and end dates.
strWhere = strField & " Between #" & Me.txtStartDate _
& "# And #" & Me.txtEndDate & "#"
End If
End If
Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
' Build filter string
strFilter = "[Category] " & strCategory & _
" AND [PmtType] " & strPayment & _
" AND [uDate] " & strWhere
 
' Apply the filter and switch it on
With Reports![repExpense]
.Filter = strFilter
.FilterOn = True
End With
End Sub
 

[Jim Doherty]

Hi,

I am a novice to VB, using Access 2003 on a XP-SP2 platform, and I have a small database for tracking expenses, and want to be able to filter my expense report by date. I currently have a form with two list boxes, purchase and payment type, and then two date fields txtStartDate and txtEndDate. Filter works fine when no dates are chosen, problem is with my date picker - if I choose dates I get a "syntax error (missing operator)" error in my strWhere statement (I have used the debugger, but can't pinpoint the syntax error.. I have posted the script for the "Apply FIlter" command, any assistance would be appreciated. By the way, my date field in my source table us uDate, and in my report is "repDate" to avoid reserved fields.

Script:

 Private Sub cmdApplyFilter_Click() 
Dim varItem As Variant
Dim strCategory As String
Dim strPayment As String
Dim strFilter As String
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
 
 
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "repExpense") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string from lstCategory listbox
For Each varItem In Me.lstCategory.ItemsSelected
strCategory = strCategory & ",'" & Me.lstCategory.ItemData(varItem) _
& "'"
Next varItem
If Len(strCategory) = 0 Then
strCategory = "Like '*'"
Else
strCategory = Right(strCategory, Len(strCategory) - 1)
strCategory = "IN(" & strCategory & ")"
End If
' Build criteria string from lstPayment listbox
For Each varItem In Me.lstPayment.ItemsSelected
strPayment = strPayment & ",'" & Me.lstPayment.ItemData(varItem) _
& "'"
Next varItem
If Len(strPayment) = 0 Then
strPayment = "Like '*'"
Else
strPayment = Right(strPayment, Len(strPayment) - 1)
strPayment = "IN(" & strPayment & ")"
End If
' Build criteria string for date
 
strReport = "repExpense"
strField = "Date"
 
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & "<=#" & Me.txtEndDate
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & ">=#" & Me.txtStartDate
Else 'Both start and end dates.
strWhere = strField & " Between #" & Me.txtStartDate _
& "# And #" & Me.txtEndDate & "#"
End If
End If
Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
' Build filter string
strFilter = "[Category] " & strCategory & _
" AND [PmtType] " & strPayment & _
" AND [uDate] " & strWhere
 
' Apply the filter and switch it on
With Reports![repExpense]
.Filter = strFilter
.FilterOn = True
End With
End Sub
 

Hi cc and welcome to the scripts!

Rather than expect people to wade through and debug your code why not simply post the results of a debug.print line examples like you have embedded there then it should be clearly obvious where your code is failing on the where clause. From briefly looking it seems you are not closing off your DATE delimiters with the hash symbol

Jim :)

[NeoPa]

Jim makes a good point. It's also helpful to know where in the code something happens (like error message or Debug.Print lines).
Anyway, have a look in Literal DateTimes and Their Delimiters (#) for a fuller understanding of how this works.