multiple criteria (1 Viewer)

[tubar]

i have two combo boxes on a form for the criteria to open the next form. I keep getting "data type mismatch in criteria expression"

stDocName = "FRMSHIFTSTART"
stLinkCriteria = "[date] = '" & Me![Combo13] & "' AND [shift] = '" & Me![Combo16] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

any suggestions

 

[pbaldy]

Basically you need the ' delimiter for text fields, # for date/time fields and none for numeric fields. Perhaps

stLinkCriteria = "[date] = #" & Me![Combo13] & "# AND [shift] = '" & Me![Combo16] & "'"

 

[CJ_London]

date is a reserved word, so it can create unexpected errors - google 'access reserved words' to find out more. However it is most likely that you are trying to compare a datetype field with a string

try changing to either

"[date] = " & cdate(Me![Combo13]) & " AND .....

or

"[date] = #" & format(Me![Combo13],"mm/dd/yyyy") & "# AND .....

note that dates as strings have to be in the US style format as above

 

[tubar]

so if shift was a numeric field would this look correct?

stLinkCriteria = "[date] = #" & Me![Combo13] & " AND [shift] = & Me![Combo16] & "

 

[pbaldy]

No, more like:

stLinkCriteria = "[date] = #" & Me![Combo13] & "# AND [shift] = " & Me![Combo16]

 

[Simon_MT]

I generally declare SearchDate as whatever field and then use the converted date in the criteria:

With CodeContextObject

Dim SearchDate as Variant
SearchDate = Format(.[Combo13]),"mm/dd/yyyy")

End With

stLinkCriteria = "[date] = #" & SearchDate & "# AND [shift] = " & Me![Combo16]

This is so I don't forget!!! I use Functions not Subs.

Simon