Type Mismatch

[eRed]

I am trying to run a report from a Form Button based on two criteria;

patient.ID and 1259rec (date)

I can make it work based on the patient.ID but not the date (nor both)

THIS WORKS
Private Sub Command70_Click()
DoCmd.OpenReport "CouponBk", acViewPreview, , "[CouponBk].[fk_patientID]=" & Me.fk_patientID
End Sub

THIS DOESN'T
Private Sub Command70_Click()
DoCmd.OpenReport "CouponBk", acViewPreview, , "[CouponBk].[fk_patientID]=" & Me.fk_patientID And "[CouponBk].[1259rec]=" & Chr(34) & Me.1259rec.Value & Chr(34)
End Sub

Any suggestions?

Thanks

 

[DCrake]

DoCmd.OpenReport "CouponBk", acViewPreview, , "[CouponBk].[fk_patientID]=" & Me.fk_patientID  & " And [CouponBk].[1259rec]='"  & Me.1259rec & "'"

The above is the correct syntax, however please tell me that you do not have fields in your table called 1259rec? and controls on a form called 1259rec?

If so you really need to think closely about naming conventions.

 

[eRed]

I do... :-/ what's wrong with those names?? I am a bit of a noob.

 

[DCrake]

The control and field names may mean something to you - hopefully. However, someone else may have difficulty understanding it.

 

[eRed]

They are the names of forms that when rec'd trigger operational events.

So the code looks correct? I have tried a number of variations on this line:

DoCmd.OpenReport "CouponBk", acViewPreview, , "[CouponBk].[fk_patientID]=" & Me.fk_patientID And "[CouponBk].[1259rec]=" & Chr(34) & Me.1259rec.Value & Chr(34)

I get:

Compile error:
Expected: end of statement

when I click OK the .1259 of Me.1259rec.Value is highlighted

... =" & Chr(34) & Me.1259rec.Value & Chr(34)

Any suggestions?

 

[Poppa Smurf]

I am not familar with using criteria for reports but here are a couple of suggestions that you could try based based on past experience using code.
If Me.1259rec is a date then you may need to use the following format [CouponBk].[1259rec]=#" & FormatMe.1259rec , "mm/dd/yyyy") & "#"

Also the AND must be enclosed by " and a space is required before the AND
"[CouponBk].[fk_patientID]=" & Me.fk_patientID & " And [CouponBk].[1259rec]=" & Chr(34) & Me.1259rec.Value & Chr(34)