report filter by set date

[copsy1979]

hi,

I have successfully filtered 2 reports with start and end dates, using AllenBrown method of txtenddate form and code.

I am now having trouble changing the code to allow it to filter by one set date. I have created the form and query correctly but when I run the report and enter a set date the report does not filter by anything at all.

has anyone got any suggestions?

Thanks,

 

[boblarson]

Post the code you are currently using.

 

[copsy1979]

Well it does work if I keep the code. But I idealy want to only enter one date.
First 2 reports required a start and end date so code is set up for this.

This report I want to enter a particular date : e.g 31/01/08. This means with using the code I enter the same date twice : in both start and end date boxes.

Whilst it is workable, it is not very useful to the people who will be using the database.

Any other ideas with what to do with the code would be appreciated?

 

[boblarson]

Any other ideas with what to do with the code would be appreciated?

That's why I asked you to post the code. We aren't psychic and able to just tell what you are currently using. How can we suggest a change to the code if we don't have the code?????

 

[copsy1979]

sorry ... a newbie to the forums. This is the code that works.

Private Sub Preview_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "rptMonthlyLoads"
strField = "InvoiceDate"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere


I ideally need to convert it to one date entry required. Thanks

 

[boblarson]

If your date text box is named txtDate then the code would be:

    Dim strReport As String    'Name of report to open.
    Dim strField As String    'Name of your date field.
    Dim strWhere As String    'Where condition for OpenReport.
    Const conDateFormat = "\#mm\/dd\/yyyy\#"
    strReport = "rptMonthlyLoads"
    strField = "InvoiceDate"

        If Not IsNull(Me.txtDate) Then    'One Date Field
            strWhere = strField & " >= " & Format(Me.txtDate, conDateFormat)
        End If

    ' Debug.Print strWhere 'For debugging purposes only.
    DoCmd.OpenReport strReport, acViewPreview, , strWhere

If you are looking for anything from that date forward. If you want it from that date backward then just change the >= to <=

 

[copsy1979]

thanks, that seems to have worked.