multiple criteria

tubar

Registered User.
Local time
Today, 11:21
Joined
Jul 13, 2006
Messages
190
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"
Code:
stDocName = "FRMSHIFTSTART"
stLinkCriteria = "[date] = '" & Me![Combo13] & "' AND [shift] = '" & Me![Combo16] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
any suggestions
 
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] & "'"
 
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
 
so if shift was a numeric field would this look correct?
Code:
stLinkCriteria = "[date] = #" & Me![Combo13] & " AND [shift] = & Me![Combo16] & "
 
No, more like:

stLinkCriteria = "[date] = #" & Me![Combo13] & "# AND [shift] = " & Me![Combo16]
 
I generally declare SearchDate as whatever field and then use the converted date in the criteria:

Code:
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
 

Users who are viewing this thread

Back
Top Bottom