Date variable in vba using sql query

Question

I have a query in VB6:

"select plate_no from INFO where date_time between #07-10-2012 01:13:17# and #10/10/2012 11:30:25#"

My Access database table has the column with datatype Data/Time in general format.

How do I modify the query above to use variables like:

Public t1 As Date 
Public t2 As Date

Out of interest, is that date the 7th of October 2012 or the 10th of July 2012? (I believe most parsers will treat it as the former) I've updated the answer to be unambigious anyway. — Deanna
– Deanna, Commented Oct 11, 2012 at 9:06

Deanna · Accepted Answer · 2012-10-11 09:06:57Z

4

I'm doing it from memory, may have to tweak it to compile:

Public t1 as Date
Public t2 as Date

t1 = #10/07/2012 01:13:17# '7th of October 2012
t2 = #10/10/2012 11:30:25# '10th of October 2012

sql = "select plate_no from INFO where date_time between #" + Format(t1, "YYYY-MM-DD HH:MM:SS") + "# and #" + Format(t2, "YYYY-MM-DD HH:MM:SS") + "#"

edited Oct 11, 2012 at 9:06

Deanna

24.4k7 gold badges76 silver badges162 bronze badges

answered Oct 10, 2012 at 21:00

Mark Stevens

2,36615 silver badges9 bronze badges

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

3 Comments

Mark Stevens Over a year ago

@DanielCook Yeah I wondered - haven't used VB in years. But figured it would work, thanks.

Deanna Over a year ago

@DanielCook Yes it should. The default date to string conversion is locale specific. the SQL libraries normally need an explicit format. I've also adjusted the date literals to be unambigious in both the VB code and the SQL. VB uses mm/dd/yyyy regardless of the locale and the yyyy-mm-dd in SQL is clear.

Daniel Over a year ago

@Deanna You're right (which you already knew). I wasn't paying enough attention and thought this question was Access specific and forgot to consider different locales.

Collectives™ on Stack Overflow

Date variable in vba using sql query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related