Select query with condition date later or equl to

[robs23]

Hi Guys,

actually quite simple question but I can't get it working. Why below doesn't work?

CurrentDb.OpenRecordset("SELECT * FROM tbMessage WHERE messageType = 2 AND expirationDate <= #12/18/2014#")

It says error and I don't know why... messageType and expriationDate are fields in table "tbMessage".

Robert

[pbaldy]

Looks fine. What's the text of the error? Presumably you're setting a recordset variable?

[robs23]

It's error 3464: Data type mismatch in criteria expression. What's interesting, I get the error only when resulting recordset wouldn't be empty. For instance, assuming there's only 1 record in "tbMessage" table of expirationDate = today's date. In this case the expresion:

CurrentDb.OpenRecordset("SELECT * FROM tbMessage WHERE messageType = 2 AND expirationDate <= #12/18/2014#")

wouldn't result in empty recordset hence there would be the error. If I changed it to:

"..expirationDate = #12/18/2014#")

or

"..expirationDate >= #12/18/2014#"

no error would be produced and it'd performed correctly. Don't really get it.

I think it might have something to do with date format. In sql it's american so mm/dd/yyyy, in the table it's dd-mm-yyyy though i'm not sure if it's not only the way it's displayed there.

Robert

[pbaldy]

If messagetype is text it would be:

CurrentDb.OpenRecordset("SELECT * FROM tbMessage WHERE messageType = '2' AND expirationDate <= #12/18/2014#")

[robs23]

Unfortunately not, messageType is long integer.

[pbaldy]

What's the actual code? You're saying it works correctly with just the date criteria? Can you post the db here?

[robs23]

Can't believe but messageType was in fact set as Text.. gotta start to pay more attention to what I do. Sorry for this topic, embarrassing really.

Robert

[pbaldy]

No worries, we've all done it.

[kemas]

CurrentDb.OpenRecordset("SELECT * FROM tbMessage WHERE [messageType] = 2 AND [expirationDate] <= #12/18/2014#")