VB Code on Form for Report Criteria not Working

[Scott B]

Greetings,

I am taking the advice I received here and trying to use a form to pass the
criteria from a form to a report instead of using a parameter in a query. I
have the report and the form with a combo box and filter button and a cancel
button. I am modifying the code from Martin Green's Office Tips website.
Here is the modified code I am trying to use. I get either no filtering
(all data) or a Data Type Mismatch error depending on whether or not I have
the where clause in it. The code is on the On Click event of the form.
Anyone have any ideas?

Thanks,
Scott B


Private Sub cmdArrival_Click()

Dim strFilter As String
Dim strArrival As String
' Check that the Guest Arrival report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptGuestArrivals") <>
acObjStateOpen Then
MsgBox "Please open the Guest Arrival report first."
Exit Sub
End If
' Build criteria string for Arrival field
If IsNull(Me.cboArrivalDate.Value) Then
strArrival = "Like '*'"
Else
strArrival = "='" & Me.cboArrivalDate.Value & "'"
End If
' WHERE clause for the filter (StayStart is a Date)
strFilter = "[StayStart]" & strArrival
' Apply the filter and switch it on
With Reports![rptGuestArrivals]
.Filter = strFilter
.FilterOn = True
End With
End Sub

 

[Guest]

Hey,

If the cboArrivalDate is a Date/Time variable, you must enclose it not in
single quotation makrs but in number signs (strArrival = "=#" &
Me.cboArrivalDate.Value & "#"). Otherwise, if it is a text or an unbound
control, you need to call CDate() on it.
By the way, in the If IsNull(Me.cboArrivalDate.Value) Then case, I'd rather
not apply a filter at all.

HTH

Chris

 

[Scott B]

Chris,

Thanks, that's it. I knew I needed to define it as a date, but I didn't
know where to put the #s.

Scott B

Hey,

If the cboArrivalDate is a Date/Time variable, you must enclose it not in
single quotation makrs but in number signs (strArrival = "=#" &
Me.cboArrivalDate.Value & "#"). Otherwise, if it is a text or an unbound
control, you need to call CDate() on it.
By the way, in the If IsNull(Me.cboArrivalDate.Value) Then case, I'd
rather
not apply a filter at all.

HTH

Chris

Greetings,

I am taking the advice I received here and trying to use a form to pass
the
criteria from a form to a report instead of using a parameter in a query.
I
have the report and the form with a combo box and filter button and a
cancel
button. I am modifying the code from Martin Green's Office Tips website.
Here is the modified code I am trying to use. I get either no filtering
(all data) or a Data Type Mismatch error depending on whether or not I
have
the where clause in it. The code is on the On Click event of the form.
Anyone have any ideas?

Thanks,
Scott B


Private Sub cmdArrival_Click()

Dim strFilter As String
Dim strArrival As String
' Check that the Guest Arrival report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptGuestArrivals") <>
acObjStateOpen Then
MsgBox "Please open the Guest Arrival report first."
Exit Sub
End If
' Build criteria string for Arrival field
If IsNull(Me.cboArrivalDate.Value) Then
strArrival = "Like '*'"
Else
strArrival = "='" & Me.cboArrivalDate.Value & "'"
End If
' WHERE clause for the filter (StayStart is a Date)
strFilter = "[StayStart]" & strArrival
' Apply the filter and switch it on
With Reports![rptGuestArrivals]
.Filter = strFilter
.FilterOn = True
End With
End Sub