compare date/time via VBA with date/time in Access DB

Question

Hi,

How to compare a date/time via VBA with a date/time in an Access DB?

The query I use

adoRS.Open "SELECT * FROM currentpositions WHERE ((currentpositions.
[dateLT])=" & "#" & date_from_message & "#" & ")", adoConn, adOpenStatic,
adLockOptimistic

I only achieve to compare a date.
Anybody an idea?

Regards
Camastanta

Answer 1

adoRS.Open "SELECT * FROM currentpositions WHERE DateValue(currentpositions.
[dateLT]) = DateValue(" & "#" & date_from_message & "#)", adoConn, adOpenStatic,
adLockOptimistic

See, if the above helps.
DateValue extracts the date part from a given date/time. So, it can be used to compare date, ignoring the time part.

Answer 2

IMHO you should never build your SQL statements using string concatenation. Instead use parameterized SQL queries. This will save you from problems like the one you are facing with date/time comparison.

Answer 3

Does your system have dates in either yyyy-mm-dd or dd-mm-yyyy format? If so see Return Dates in US #mm/dd/yyyy# format to convert your dates in mm-dd-yyyy so Access can work with them properly.