Report not filtering correctly with date inputs

[michane]

My report generates base on 2 date inputs(from textboxes) namely, ProcStartDate & ProcEndDate & a combobox(cmbRptSupplier) to select the supplier:

Private Sub btnReport_Click()
    Dim strDocName, strWHERECondition As String
    strWHERECondition = ""
If Me.cmbRptSupplier = "" Or IsNull(Me.cmbRptSupplier) = True Then
        strDocName = "rpt_Invoice_Details"
        strWHERECondition = "Date_Received >= #" & ProcStartDate & "# AND Date_Received <= #" & ProcEndDate & "#"
        
    Else
        strDocName = "rpt_Invoice_Details"
        strWHERECondition = "Date_Received >= #" & ProcStartDate & "# AND Date_Received <= #" & ProcEndDate & "#" _
        & " AND Supplier_ID = " & Me.cmbRptSupplier
    End If
DoCmd.OpenReport strDocName, acPreview, , strWHERECondition
End Sub

When I leave my cmbRptSupplier blank (so that I can leave supplier out of my criteria), & fill in 1/2/2013 for ProcStartDate, 14/3/2013 for ProcEndDate, I get ALL the invoices displayed in return for my report. Even with those before 1/2/2013.

I cannot figure out how is this so

 

[michane]

Now I am thinking it could have read my date input 1/2/2013 as 2/1/2013 ?

 

[pr2-eugin]

Yes that is what it should.. Access expects all input to be in US date format if you meant the date to be 1 Feb 2013, there is a very high possibility that it might confuse itself with 2 Jan 2013.. Look into this link on "International Date problems", by Allen Browne for more info.. in the mean time try the following code, I have made some changes (including the date format)..

Private Sub btnReport_Click()
    Dim strDocName[COLOR=Blue] As String[/COLOR], strWHERECondition As String
    strWHERECondition = ""
    strDocName = "rpt_Invoice_Details"
    If [COLOR=Blue]Me.cmbRptSupplier.ListIndex = -1[/COLOR] Then
        strWHERECondition = "Date_Received [COLOR=Blue]BETWEEN[/COLOR] #" & [COLOR=Blue]Format(Me.ProcStartDate, "mm\/dd\/yyyy")[/COLOR] & "# AND #" &[COLOR=Blue] Format(Me.ProcEndDate, "mm\/dd\/yyyy")[/COLOR] & "#"
    Else
        strWHERECondition = "Supplier_ID = " & Me.cmbRptSupplier & " AND Date_Received [COLOR=Blue]BETWEEN[/COLOR] #" & [COLOR=Blue]Format(Me.ProcStartDate, "mm\/dd\/yyyy")[/COLOR] & "# AND #" & [COLOR=Blue]Format(Me.ProcEndDate, "mm\/dd\/yyyy")[/COLOR] & "#"
    End If
    DoCmd.OpenReport strDocName, acPreview, , strWHERECondition
End Sub

 

[michane]

Thank you for that Paul, it worked really well.
Do u know the coding to clear my report results from previous search, before the user uses the form for next one?

My only problem now is actually, when I do a search with both my date textboxes (ProcStartDate & ProcEndDate) input as 14-03-2013, without selecting any supplier, my report keeps showing in its result, the 14-03-2013 invoices of one of the suppliers'.. I just find this really absurd :banghead:

 

[pr2-eugin]

I normally do not use Filters.. Is your report based on a Query? If so I would create the Query dynamically and set it to the Report on its open event, instead of passing Where on the DoCmd.Open method.. This way, if you open the report it always will be filtered based on the input on the form, but if opened directly will always be opening with all details.. Makes sense?

 

[michane]

Ok, I am trying to put it in a query instead. Do you think if this is ok.. I might be lost in the brackets :

SELECT Invoice_Details.*, Invoice_Details.Date_Received
FROM Invoice_Details
WHERE (((Invoice_Details.Date_Received) BETWEEN #(Format([Forms]![frm_Reports]![ProcStartDate], "mm\/dd\/yyyy"))# AND #(Format([Forms]![frm_Reports]![ProcEndDate], "mm\/dd\/yyyy"))#));

 

[pr2-eugin]

Well you are there somewhere near.. Just a basic query would be..

SELECT Invoice_Details.* FROM Invoice_Details

Then save it, say Qry_InvoiceRpt.. Then on your report set the Query as the Data source.. In the Form's button click keep the same code with just a simple change, as follows..

Private Sub btnReport_Click()
    Dim strDocName As String, strWHERECondition As String
    [COLOR=Blue]Dim strQry As String
    strQry = "SELECT Invoice_Details.*, Invoice_Details.Date_Received FROM Invoice_Details "[/COLOR]
    strWHERECondition = ""
    strDocName = "rpt_Invoice_Details"
    If Me.cmbRptSupplier.ListIndex = -1 Then
        strWHERECondition = "WHERE Date_Received BETWEEN #" & Format(Me.ProcStartDate, "mm\/dd\/yyyy") & "# AND #" & Format(Me.ProcEndDate, "mm\/dd\/yyyy") & "#"
    Else
        strWHERECondition = "WHERE Supplier_ID = " & Me.cmbRptSupplier & " AND Date_Received BETWEEN #" & Format(Me.ProcStartDate, "mm\/dd\/yyyy") & "# AND #" & Format(Me.ProcEndDate, "mm\/dd\/yyyy") & "#"
    End If
    DoCmd.OpenReport strDocName, acPreview, [COLOR=Blue]OpenArgs:= strQry & strWHERECondition[/COLOR]
End Sub

Then in the Report Open event check the open args.. If it is not Null then you set it, which will be the query..

Private Sub Report_Open(Cancel As Integer)
    If Len(Me.OpenArgs & vbNullString) <> 0 Then
        Me.RecordSource = Me.OpenArgs
    End If
End Sub

Hope this helps..

 

[michane]

Thank you!
I tried & tested & keep trying, it just keeps showing, Run-time error '3079':
The specific field 'Date_Received' could refer to more than one table listed in the FROM clause of your SQL statement.

 

[pr2-eugin]

Try this as your WHERE clause..

    If Me.cmbRptSupplier.ListIndex = -1 Then
        strWHERECondition = "WHERE [COLOR=Red][B]Invoice_Details.[/B][/COLOR]Date_Received BETWEEN #" & Format(Me.ProcStartDate, "mm\/dd\/yyyy") & "# AND #" & Format(Me.ProcEndDate, "mm\/dd\/yyyy") & "#"
    Else
        strWHERECondition = "WHERE [COLOR=Red][B]Invoice_Details.[/B][/COLOR]Supplier_ID = " & Me.cmbRptSupplier & " AND [COLOR=Red][B]Invoice_Details.[/B][/COLOR]Date_Received BETWEEN #" & Format(Me.ProcStartDate, "mm\/dd\/yyyy") & "# AND #" & Format(Me.ProcEndDate, "mm\/dd\/yyyy") & "#"
    End If

 

[michane]

I found that the problem actually lies here, I have changed FROM:

[COLOR=#0000ff]strQry = "SELECT Invoice_Details.*, Invoice_Details.Date_Received FROM Invoice_Details "[/COLOR]

TO:

strQry = "SELECT [COLOR=red]Invoice_Details.*[/COLOR] FROM Invoice_Details "

Eventually tidied up my code to:

Private Sub btnReport_Click()
    Dim strDocName As String, strQry As String
    strDocName = "rpt_Invoice_Details"
    strQry = "SELECT Invoice_Details.* FROM Invoice_Details "
 On Error GoTo report_err
    If Me.cmbRptSupplier.ListIndex = -1 Then
        strQry = strQry & "WHERE Invoice_Details.Date_Received BETWEEN #" & Format(Me.ProcStartDate, "mm\/dd\/yyyy") & "# AND #" & Format(Me.ProcEndDate, "mm\/dd\/yyyy") & "#"
    Else
        strQry = strQry & "WHERE Invoice_Details.Supplier_ID = " & Me.cmbRptSupplier & " AND Invoice_Details.Date_Received BETWEEN #" & Format(Me.ProcStartDate, "mm\/dd\/yyyy") & "# AND #" & Format(Me.ProcEndDate, "mm\/dd\/yyyy") & "#"
    End If
    DoCmd.OpenReport strDocName, acPreview, , , , OpenArgs:=strQry
report_err:
        Exit Sub
End Sub

Behind the reports page, I had:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There are no records found from the search"
    Cancel = True
    Exit Sub
End Sub
Private Sub Report_Open(Cancel As Integer)
    If Len(Me.OpenArgs & vbNullString) <> 0 Then
        Me.RecordSource = Me.OpenArgs
    End If
End Sub

Much thanks to Paul for the assistance!

 

[pr2-eugin]

Much thanks to Paul for the assistance!

Glad to have helped.. Good Luck..