Data type mismatch error when running sql query

schniggeldorf

Registered User.
Local time
Today, 08:17
Joined
Jan 7, 2013
Messages
22
Hi:

I have vba code that creates the following SQL:

SELECT SubscheduleID, EventID, WeekOrder, DayID, StartTime, EndTime, Priority, CanJoin, PatientTitle, PatientNickname, IncludesPatient, IncludesAftercare, Letter1

FROM [qryScheduleCombinedDetails]

WHERE (SubscheduleID = 1 AND IncludesPatient = -1 AND DuringAftercare <> "AC only" AND (WeekOrder = "All" OR WeekOrder = 3 OR (WeekOrder = 1 AND Letter1 = "XYZ")) AND DayID = 2 AND StartTime <= #8:00:00 AM# AND EndTime >= #8:30:00 AM#);

When I try to run it, I get a "data type mismatch" error.

When I put the same code into a query, I get the same error. However, it will run if I delete either condition from within the (WeekOrder = 1 AND Letter1 = "XYZ") pairing. I can't figure why it can run with either of those, but not both together.

WeekOrder is defined as String. Letter1 is calculated as Cstr(Nz(IIf(Letter,"XYZ","ABC"))) within [qryScheduleCombinedDetails], because I wanted to make sure that it would be recognized as a string.

I've spent the last 6 hours trying to solve this, but I'm getting nowhere. Does anybody have any ideas?

Thanks.
 
Weekorder appears to be a text field

WeekOrder = "All" OR WeekOrder = "3"

etc
 
I thought of the possibility that it's a problem because WeekOrder is indeed a text field. However, if that's the problem, why does deleting the Letter1="XYZ" specification solve the problem, even though the WeekOrder=3 remains intact?
 
I tried your idea, even though I didn't see how it could work, but apparently it did. Thank you.

Any idea why deleting parts of the criteria made the error go away, even though there were still numeric data being sent as criteria for a text field?
 
VBA will cast to wider types such as converting an integer to a string. Usually SQL is more strict.

Maybe it would forgive you one mistake but for more it decided you needed a kick.;)
 

Users who are viewing this thread

Back
Top Bottom