Multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am filtering a recordset to obtain a single report. This is my code:

Dim datConsultDate As Date
Dim lngPatientID As Long

datConsultDate = CDate(Forms!frmConsultation!txtConsultDate)
lngPatientID = Me!PatientID
DoCmd.OpenReport "rptReferralLetter", acViewPreview, , "[PatientID] = " &
lngPatientID & " And [ConsultDate] = #" & datConsultDate & "#"

I'm getting a run time error 3079 and 'Reserved error'

Any help greatly appreciated.

Sandy
 
See if this helps:

Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If IsDate(Forms!frmConsultation!txtConsultDate) And Not
IsNull(me.PatientID) Then
strWhere = "(PatientID = " & Me.PatientID & ") AND (ConsultDate = #"
& _
Format(Forms!frmConsultation!txtConsultDate, "mm\/dd\/yyyy") &
"#)"
Debug.Print strWhere
DoCmd.OpenReport "rptReferralLetter", acViewPreview, , strWhere
Else
MsgBox "Date and patient number required."
End If

It helps to build the WhereCondition as string, so you can see it in the
Immediate Window (Ctrl+G) if things don't work. Testing for a valid date and
number may also avoid errors. Saving the current record may also help.
Formatting the date can help if your date format is non-US.
 
Allen

The date formatting made a difference (I'm using UK formatting) and I also
had to alter strWhere to

strWhere = "(tblReferral.PatientID = " & Me.PatientID & ") AND
(tblConsultations.ConsultDate = #" & _
Format(Forms!frmConsultation!txtConsultDate, "mm\/dd\/yyyy") & "#)"

before the problem was resolved.

Thanks very much indeed

Allen Browne said:
See if this helps:

Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If IsDate(Forms!frmConsultation!txtConsultDate) And Not
IsNull(me.PatientID) Then
strWhere = "(PatientID = " & Me.PatientID & ") AND (ConsultDate = #"
& _
Format(Forms!frmConsultation!txtConsultDate, "mm\/dd\/yyyy") &
"#)"
Debug.Print strWhere
DoCmd.OpenReport "rptReferralLetter", acViewPreview, , strWhere
Else
MsgBox "Date and patient number required."
End If

It helps to build the WhereCondition as string, so you can see it in the
Immediate Window (Ctrl+G) if things don't work. Testing for a valid date and
number may also avoid errors. Saving the current record may also help.
Formatting the date can help if your date format is non-US.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sandy said:
I am filtering a recordset to obtain a single report. This is my code:

Dim datConsultDate As Date
Dim lngPatientID As Long

datConsultDate = CDate(Forms!frmConsultation!txtConsultDate)
lngPatientID = Me!PatientID
DoCmd.OpenReport "rptReferralLetter", acViewPreview, , "[PatientID] = " &
lngPatientID & " And [ConsultDate] = #" & datConsultDate & "#"

I'm getting a run time error 3079 and 'Reserved error'

Any help greatly appreciated.

Sandy
Click to expand...
Click to expand...
 
Back
Top