troubleshooting Question

Access 2007 VBA form query report function troubleshooting help needed

Avatar of gbnorton
gbnortonFlag for United States of America asked on
Microsoft Access
4 Comments1 Solution258 ViewsLast Modified:
The attached code was borrowed to run a report by first getting the date range from a form.  I am trying to modify it to also include the Lot Number criteria.

The form has 3 txt boxes:  txtLotNumber, txtStartDate, txtEndDate

It works fine using the dates part, but when I add the Lot Number.... when the report starts to open I am prompted for txtLotNumber.

I can't see what I am missing.  The function code is attached.
Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    '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 strLotNumber As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    'DO set the values in the next 3 lines.
    strReport = "Lot Number By Date Range"      'Put your report name in these quotes.
    strDateField = "datevalue([DateTimeOpened])" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
    'Build the filter string.
    strLotNumber = "([LotNumber] = Me.txtLotNumber)"
    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) & ")"
        strWhere = strWhere & " AND " & strLotNumber
    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 strReport, lngView, , strWhere

    Exit Sub

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

End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros