By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,823 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

generate report based on date range

P: 68
Hi everyone,
I have an Access database with a table and a date field, and I also have a report for this table. I am getting error from my code on getting the exact date range I need to be generated from the table into the report.
Here is my code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDateRange_Click()
  2. On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
  3.     'Purpose:       Filter a report to a date range.
  4.     'Documentation: http://allenbrowne.com/casu-08.html
  5.     'Note:          Filter uses "less than the next day" in case the field has a time component.
  6.     Dim strTable As String
  7.     Dim strDateField As String
  8.     Dim strWhere As String
  9.     Dim lngView As Long
  10.     Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
  11.  
  12.     'DO set the values in the next 3 lines.
  13.     strTable = "Mutual Ensured Medical Report"      'Put your report name in these quotes.
  14.     strDateField = "[txtDateOfVisit]" 'Put your field name in the square brackets in these quotes.
  15.     lngView = acViewReport     'Use acViewNormal to print instead of preview.
  16.  
  17.     'Build the filter string.
  18.     If IsDate(Me.txtStartDate) Then
  19.         strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
  20.     End If
  21.     If IsDate(Me.txtEndDate) Then
  22.         If strWhere <> vbNullString Then
  23.             strWhere = strWhere & " AND "
  24.         End If
  25.         strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
  26.     End If
  27.  
  28.     'Close the report if already open: otherwise it won't filter properly.
  29.     If CurrentProject.AllReports(strReport).IsLoaded Then
  30.         DoCmd.Close acTable, strTable
  31.     End If
  32.  
  33.     'Open the report.
  34.     Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
  35.     DoCmd.OpenReport "Mutual Insured Medical Report", acViewReport
  36.     txtStartDate = ""
  37.     txtEndDate = ""
  38.  
  39. Exit_Handler:
  40.     Exit Sub
  41.  
  42. Err_Handler:
  43.     If Err.Number <> 2501 Then
  44.         MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
  45.     End If
  46.     Resume Exit_Handler
  47. End Sub
any help?
Oct 2 '17 #1

✓ answered by PhilOfWalton

Quick thought.

You are comparing dates as strings so that "15/05/2017" is less than "20/03/2017"
Remember that a formatted date is a string.

You need to convert everything to dates.

It mat be worth your while to consider using the date picker to input your date range.

Phil

Share this Question
Share on Google+
3 Replies


PhilOfWalton
Expert 100+
P: 1,430
Quick thought.

You are comparing dates as strings so that "15/05/2017" is less than "20/03/2017"
Remember that a formatted date is a string.

You need to convert everything to dates.

It mat be worth your while to consider using the date picker to input your date range.

Phil
Oct 2 '17 #2

P: 68
Thanks Phil, I got is sorted out.
Oct 2 '17 #3

NeoPa
Expert Mod 15k+
P: 31,419
Another format I find very useful for SQL dates is a different SQL standard that is completely different from any standards used generally for display around the world, and that is yyyy-m-d (See Literal DateTimes and Their Delimiters (#) for more). No danger anyone from America will confuse it with their standard display settings.
Oct 2 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.