Open report between dates OR specific date

Question

Hello

I have a query that provides records for a report.

On the report is a date

I have a form that has a button to open the report

On the form are 3 unbound text boxes ( txtStartDate txtEndDate txtSpecificDate )

The OnChange event of txtStartDate and txtEndDate is txtSpecificDate=Null

The OnChange event of txtSpecificDate  is txtStartDate=Null and txtEndDate=Null

This way I can "either" put a start date and end date OR a specific date

What would be the VBA to open the report with EITHER a start and end  OR a specific date

I have tried lots of combinations and I am getting nowhere.

Thank you

Answer 1

Try this:

Private Sub cmdOpenReport_Click()

    Dim strWhere As String

    If Not IsNull(Me.txtStartDate) And Not IsNull(Me.txtEndDate) Then

        strWhere = "[DateField] Between #" & Format(Me.txtStartDate, _

            "mm/dd/yyyy") & "# And #" & Format(Me.txtEndDate, _

            "mm/dd/yyyy") & "#"

    ElseIf Not IsNull(Me.txtSpecificDate) Then

        strWhere = "[DateField] = #" & Format(Me.txtSpecificDate, _

            "mm/dd/yyyy") & "#"

    Else

        MsgBox "Please fill in either start and end date or specific date"

        Exit Sub

    End If

    DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, _

        WhereCondition:=strWhere

End Sub

Substitute the correct names for the command button (cmdOpenReport), the date/time field (DateField) and the report (rptMyReport).

Answer 2

Thank you