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

Question

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

What format are your dates in?

Shaun Mason
– Shaun Mason

2009-12-21 18:49:37 +00:00
Commented Dec 21, 2009 at 18:49 — Shaun Mason
– Shaun Mason, Commented Dec 21, 2009 at 18:49

shahkalpesh · Accepted Answer · 2009-12-21 18:55:59Z

3

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.

answered Dec 21, 2009 at 18:55

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jakob Christensen · Accepted Answer · 2009-12-21 19:04:35Z

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.

answered Dec 21, 2009 at 19:04

Jakob Christensen

15k2 gold badges53 silver badges82 bronze badges

3 Comments

Tony Toews Over a year ago

I disagree. Using parameterized SQL queries is much harder to work with when you have to go back to maintain and test the code.

David-W-Fenton Over a year ago

I would say it depends on where the value you're plugging into the string is coming from. If it's coming from a control the user can't type free text into, you're completely safe in Access. A text field with a date mask or that can only be edited with a date picker control will never be subject to SQL injection in any form.

Jakob Christensen Over a year ago

@Tony: Why do you find it harder to maintain? @David: I don't think it is just a matter of SQL injection. It also saves you from date formatting problems etc. Also, if your Access application is backed by a SQL Server, parameterized queries allow SQL Server to cache the query plans which gives you better performance.

Tony Toews · Accepted Answer · 2009-12-21 22:50:41Z

0

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.

answered Dec 21, 2009 at 22:50

Tony Toews

7,8931 gold badge24 silver badges27 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related