Search Data by date range

[Sunnylei]

I have problem with code of search data by date range. The code works well with Access 2003, but not working for Access 2007. When I run the code with Access 2007, the message box shows 'run time error: 13, error type mismatch (see highlighted error below). The entire code is as follows,

Private Sub Command1_Click()
'Purpose: Filter a report to a date range.
'Note: Filter uses "less than the next day" in case the field has a time component.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"
 
'DO set the values in the next 3 lines.
strReport = "rptWorkshop" 'Put your report name in these quotes.
strDateField = "[DateOfWork]" 'Put your field name in the square brackets in these quotes.
lngView = acViewPreview 'Use acViewNormal to print instead of preview.
 
'Build the filter string.
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
[COLOR=red]strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"[/COLOR]
End If
 
'Close the report if already open: otherwise it won't filter properly.
 
If CurrentProject.AllReports(strReport).IsLoaded Then
 
DoCmd.Close acReport, strReport
End If
 
'Open the report.
'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
 
DoCmd.OpenReport "rptWorkshop", acViewPreview, , strWhere
 
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

 

[pr2-eugin]

I believe your error is here..

Const strcJetDate = "\#mm\/dd\/yyyy\#"

Try this..

Const strcJetDate = "mm/dd/yyyy"

 

[Sunnylei]

I believe your error is here..

Const strcJetDate = "\#mm\/dd\/yyyy\#"

Try this..

Const strcJetDate = "mm/dd/yyyy"

Hi
It still not working.

 

[jdraw]

Your code may be easier to read with some indenting

Private Sub Command1_Click()
'Purpose: Filter a report to a date range.
'Note: Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"

    'DO set the values in the next 3 lines.
    strReport = "rptWorkshop"    'Put your report name in these quotes.
    strDateField = "[DateOfWork]"    'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview    'Use acViewNormal to print instead of preview.

    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If

    'Close the report if already open: otherwise it won't filter properly.

    If CurrentProject.AllReports(strReport).IsLoaded Then

        DoCmd.Close acReport, strReport
    End If

    'Open the report.
    'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.

    DoCmd.OpenReport "rptWorkshop", acViewPreview, , strWhere

Exit_Handler:
    Exit Sub
Err_Handler:
    If Err.number <> 2501 Then
        MsgBox "Error " & Err.number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

I would recommend adjusting the code to use the
BETWEEN date1 AND date2 construct

see example 2 at
http://www.techonthenet.com/sql/between.php

 

[Sunnylei]

Your code may be easier to read with some indenting

Private Sub Command1_Click()
'Purpose: Filter a report to a date range.
'Note: Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
 
    'DO set the values in the next 3 lines.
    strReport = "rptWorkshop"    'Put your report name in these quotes.
    strDateField = "[DateOfWork]"    'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview    'Use acViewNormal to print instead of preview.
 
    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If
 
    'Close the report if already open: otherwise it won't filter properly.
 
    If CurrentProject.AllReports(strReport).IsLoaded Then
 
        DoCmd.Close acReport, strReport
    End If
 
    'Open the report.
    'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
 
    DoCmd.OpenReport "rptWorkshop", acViewPreview, , strWhere
 
Exit_Handler:
    Exit Sub
Err_Handler:
    If Err.number <> 2501 Then
        MsgBox "Error " & Err.number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

I would recommend adjusting the code to use the
BETWEEN date1 AND date2 construct

see example 2 at
http://www.techonthenet.com/sql/between.php

Sorry I tried it. It still not working.

 

[Sunnylei]

Sorry I tried it. It still not working.

Hi
I'm learning Access. Can you let me know where to put BETWEEN date1 AND date2 construct
Query or Events?

Thanks