Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Filtering Access report by dates

Newbie
  
Join Date: Jan 2008
Posts: 1
#1: Jan 21 '08
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:

Expand|Select|Wrap|Line Numbers
  1.  Private Sub cmdApplyFilter_Click() 
  2. Dim varItem As Variant
  3. Dim strCategory As String
  4. Dim strPayment As String
  5. Dim strFilter As String
  6. Dim strReport As String 'Name of report to open.
  7. Dim strField As String 'Name of your date field.
  8. Dim strWhere As String 'Where condition for OpenReport.
  9.  
  10.  
  11. ' Check that the report is open
  12. If SysCmd(acSysCmdGetObjectState, acReport, "repExpense") <> acObjStateOpen Then
  13. MsgBox "You must open the report first."
  14. Exit Sub
  15. End If
  16. ' Build criteria string from lstCategory listbox
  17. For Each varItem In Me.lstCategory.ItemsSelected
  18. strCategory = strCategory & ",'" & Me.lstCategory.ItemData(varItem) _
  19. & "'"
  20. Next varItem
  21. If Len(strCategory) = 0 Then
  22. strCategory = "Like '*'"
  23. Else
  24. strCategory = Right(strCategory, Len(strCategory) - 1)
  25. strCategory = "IN(" & strCategory & ")"
  26. End If
  27. ' Build criteria string from lstPayment listbox
  28. For Each varItem In Me.lstPayment.ItemsSelected
  29. strPayment = strPayment & ",'" & Me.lstPayment.ItemData(varItem) _
  30. & "'"
  31. Next varItem
  32. If Len(strPayment) = 0 Then
  33. strPayment = "Like '*'"
  34. Else
  35. strPayment = Right(strPayment, Len(strPayment) - 1)
  36. strPayment = "IN(" & strPayment & ")"
  37. End If
  38. ' Build criteria string for date
  39.  
  40. strReport = "repExpense"
  41. strField = "Date"
  42.  
  43. If IsNull(Me.txtStartDate) Then
  44. If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
  45. strWhere = strField & "<=#" & Me.txtEndDate
  46. End If
  47. Else
  48. If IsNull(Me.txtEndDate) Then 'Start date, but no End.
  49. strWhere = strField & ">=#" & Me.txtStartDate
  50. Else 'Both start and end dates.
  51. strWhere = strField & " Between #" & Me.txtStartDate _
  52. & "# And #" & Me.txtEndDate & "#"
  53. End If
  54. End If
  55. Debug.Print strWhere 'For debugging purposes only.
  56. DoCmd.OpenReport strReport, acViewPreview, , strWhere
  57. ' Build filter string
  58. strFilter = "[Category] " & strCategory & _
  59. " AND [PmtType] " & strPayment & _
  60. " AND [uDate] " & strWhere
  61.  
  62. ' Apply the filter and switch it on
  63. With Reports![repExpense]
  64. .Filter = strFilter
  65. .FilterOn = True
  66. End With
  67. End Sub
  68.  
Jim Doherty's Avatar
Moderator
  
Join Date: Aug 2007
Location: Derbyshire,England
Posts: 639
#2: Jan 22 '08

re: Filtering Access report by dates

Quote:

Originally Posted by 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:

Expand|Select|Wrap|Line Numbers
  1.  Private Sub cmdApplyFilter_Click() 
  2. Dim varItem As Variant
  3. Dim strCategory As String
  4. Dim strPayment As String
  5. Dim strFilter As String
  6. Dim strReport As String 'Name of report to open.
  7. Dim strField As String 'Name of your date field.
  8. Dim strWhere As String 'Where condition for OpenReport.
  9.  
  10.  
  11. ' Check that the report is open
  12. If SysCmd(acSysCmdGetObjectState, acReport, "repExpense") <> acObjStateOpen Then
  13. MsgBox "You must open the report first."
  14. Exit Sub
  15. End If
  16. ' Build criteria string from lstCategory listbox
  17. For Each varItem In Me.lstCategory.ItemsSelected
  18. strCategory = strCategory & ",'" & Me.lstCategory.ItemData(varItem) _
  19. & "'"
  20. Next varItem
  21. If Len(strCategory) = 0 Then
  22. strCategory = "Like '*'"
  23. Else
  24. strCategory = Right(strCategory, Len(strCategory) - 1)
  25. strCategory = "IN(" & strCategory & ")"
  26. End If
  27. ' Build criteria string from lstPayment listbox
  28. For Each varItem In Me.lstPayment.ItemsSelected
  29. strPayment = strPayment & ",'" & Me.lstPayment.ItemData(varItem) _
  30. & "'"
  31. Next varItem
  32. If Len(strPayment) = 0 Then
  33. strPayment = "Like '*'"
  34. Else
  35. strPayment = Right(strPayment, Len(strPayment) - 1)
  36. strPayment = "IN(" & strPayment & ")"
  37. End If
  38. ' Build criteria string for date
  39.  
  40. strReport = "repExpense"
  41. strField = "Date"
  42.  
  43. If IsNull(Me.txtStartDate) Then
  44. If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
  45. strWhere = strField & "<=#" & Me.txtEndDate
  46. End If
  47. Else
  48. If IsNull(Me.txtEndDate) Then 'Start date, but no End.
  49. strWhere = strField & ">=#" & Me.txtStartDate
  50. Else 'Both start and end dates.
  51. strWhere = strField & " Between #" & Me.txtStartDate _
  52. & "# And #" & Me.txtEndDate & "#"
  53. End If
  54. End If
  55. Debug.Print strWhere 'For debugging purposes only.
  56. DoCmd.OpenReport strReport, acViewPreview, , strWhere
  57. ' Build filter string
  58. strFilter = "[Category] " & strCategory & _
  59. " AND [PmtType] " & strPayment & _
  60. " AND [uDate] " & strWhere
  61.  
  62. ' Apply the filter and switch it on
  63. With Reports![repExpense]
  64. .Filter = strFilter
  65. .FilterOn = True
  66. End With
  67. End Sub
  68.  

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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,722
#3: Jan 22 '08

re: Filtering Access report by dates

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.
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,411 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved