Printing a report using Text Boxes

[Wong]

Can anyone help with the following vba:

I have a form with numerous reports that use the values from 3 text boxes.

I now need to create more reports but this time based only on 2 of the values.

The text boxes are called txtDateTo, txtDateFrom and cmbActivity. I now only need the date to and from for the new reports.

This is a copy of the vba I have put in:


Private Sub cmdrptConfLtrAll_Click()

Dim dtDateFrom As Date
Dim dtDateTo As Date
Dim strSql As String

'Check criteria are filled in properly
If Not IsNull(Me.txtDateFrom) Then
dtDateFrom = Format(Me.txtDateFrom, "mm/dd/yyyy")
Else
MsgBox " You must enter a date in the Date From box to proceed", vbCritical, "Missing Date Range"
Exit Sub
End If
If Not IsNull(Me.txtDateTo, "mm/dd/yyyy") Then
dtDateTo = Format(Me.txtDateTo, "mm/dd/yyyy")
Else
MsgBox " You must enter a date in the Date To box to proceed", vbCritical, "Missing Date Range"
Exit Sub
End If

strSql = "tblSchoolActivities.[Activity Date] BETWEEN #" & dtDateFrom & "# AND #" & _
dtDateTo & ""

'Open the report
strReportname = "rptConfLetterAll"
DoCmd.OpenReport strReportname, acViewPreview, , strSql



End Sub

Quite new to this as you can probably tell, but any help or advise would be appreciated.

Thanks
Sue

 

[WayneRyan]

Sue,

You don't have to convert the dates and introduce new variables.
Also, if they enter a date, then remove it, the field won't be Null,
it will be empty "".

Private Sub cmdrptConfLtrAll_Click()

If Nz(Me.txtDateFrom) = "" Then
  MsgBox " You must enter a date in the Date From box to proceed", vbCritical, "Missing Date Range"
  Exit Sub
End If

If Nz(Me.txtDateTo) = "" Then
  MsgBox " You must enter a date in the Date To box to proceed", vbCritical, "Missing Date Range"
  Exit Sub
End If

DoCmd.OpenReport "rptConfLetterAll", acViewPreview, , "tblSchoolActivities.[Activity Date] BETWEEN #" & Me.txtDateFrom & "# AND #" & Me.txtDateTo & "#"

End Sub

Wayne