Compare DateTime variables in SQL query - VBA

[npa3000]

Hey, i want to run a query that uses a DateTime criteria as follows:

Dim SQL As String
Dim rs As Recordset
clientId = Me.CLIENT_ID
supplierId = Me.SUPPLIER_ID
dateIss = Me.AddedAt

SQL = "SELECT TRANSACTIONS.ID FROM TRANSACTIONS WHERE (TRANSACTIONS.TRANS_CAT = 1 OR 3 OR 4) AND TRANSACTIONS.CLIENT_ID= " & clientId & " AND TRANSACTIONS.SUPPLIER_ID= " & supplierId & " AND TRANSACTIONS.addedDate >= '" & dateIss & "'"

Set rs = CurrentDb.OpenRecordset(SQL)

But i get this error: "Data type mismatch in criteria expresssion"

Any ideas?

 

[theDBguy]

Hi. Dates in Access need the # delimiters. You could try it this way:

SQL = "SELECT TRANSACTIONS.ID FROM TRANSACTIONS WHERE (TRANSACTIONS.TRANS_CAT = 1 OR 3 OR 4) AND TRANSACTIONS.CLIENT_ID= " & clientId & " AND TRANSACTIONS.SUPPLIER_ID= " & supplierId & " AND TRANSACTIONS.addedDate >= #" & Format(CDate(dateIss), "yyyy-mm-dd") & "#"

 

[npa3000]

Hi. Dates in Access need the # delimiters. You could try it this way:

SQL = "SELECT TRANSACTIONS.ID FROM TRANSACTIONS WHERE (TRANSACTIONS.TRANS_CAT = 1 OR 3 OR 4) AND TRANSACTIONS.CLIENT_ID= " & clientId & " AND TRANSACTIONS.SUPPLIER_ID= " & supplierId & " AND TRANSACTIONS.addedDate >= #" & Format(CDate(dateIss), "yyyy-mm-dd") & "#"

Thanks!
It works, but my field also saves the time... How can i fix that ?

 

[theDBguy]

Thanks!
It works, but my field also saves the time... How can i fix that ?

Just a guess, but maybe try changing this part?

...& " AND TRANSACTIONS.addedDate >= #" & Format(DateValue(dateIss), "yyyy-mm-dd") & "#"

 

[Gasman]

I wouldn't have thought you would want the time.?
If you have a dateIss as 04/10/2021 12:00 and addedDate was today, you would miss that record?

 

[MarkK]

Also, this probably doesn't do what you think it does...

(TRANSACTIONS.TRANS_CAT = 1 OR 3 OR 4)

 

[The_Doc_Man]

I'll help with MarkK's comment. His comment relates to the semantics of what you wrote.

(TRANSACTIONS.TRANS_CAT = 1 OR 3 OR 4) will be evaluated as a value that is either TRUE or FALSE based on whether TRANS_CAT is or isn't 1. Then it will be LOGICALLY OR'd with 3, which will equal either TRUE or 3. THAT result will be logically OR'd with 4, leaving you with either TRUE or 7. TRUE is -1 as the result of a Boolean expression. So your possible results are -1 or 7. BOTH of which evaluate as TRUE since only 0 is FALSE.

To make that work, you would need (using shorter notation) TRANS_CAT = 1 OR TRANS_CAT = 3 OR TRANS_CAT = 4 (if that is really what you wanted to test.)

 

[Grumm]

I'll help with MarkK's comment. His comment relates to the semantics of what you wrote.

(TRANSACTIONS.TRANS_CAT = 1 OR 3 OR 4) will be evaluated as a value that is either TRUE or FALSE based on whether TRANS_CAT is or isn't 1. Then it will be LOGICALLY OR'd with 3, which will equal either TRUE or 3. THAT result will be logically OR'd with 4, leaving you with either TRUE or 7. TRUE is -1 as the result of a Boolean expression. So your possible results are -1 or 7. BOTH of which evaluate as TRUE since only 0 is FALSE.

To make that work, you would need (using shorter notation) TRANS_CAT = 1 OR TRANS_CAT = 3 OR TRANS_CAT = 4 (if that is really what you wanted to test.)

Not sure if SQL OR works like that.
He basically wrote :
( (TRANSACTIONS.TRANS_CAT = 1) OR (3) OR (4) )

If you want the solution you mention, you can use IN(...) too
like TRANS_CAT IN (1,3,4)

 

[npa3000]

Just a guess, but maybe try changing this part?

...& " AND TRANSACTIONS.addedDate >= #" & Format(DateValue(dateIss), "yyyy-mm-dd") & "#"

No, it doesn't work

 

[npa3000]

I wouldn't have thought you would want the time.?
If you have a dateIss as 04/10/2021 12:00 and addedDate was today, you would miss that record?

Yes...

 

[arnelgp]

SQL = "SELECT TRANSACTIONS.ID FROM TRANSACTIONS WHERE ((TRANSACTIONS.TRANS_CAT) IN (1,3,4)) AND ((TRANSACTIONS.CLIENT_ID)= " & clientId & ") AND ((TRANSACTIONS.SUPPLIER_ID)= " & supplierId & ") AND ((TRANSACTIONS.addedDate) >= #" & format(dateIss,"mm/dd/yyyy") & "#)"