MS Access Date not working in VBA SQL query

Question

There is a table in MS Access having 3 columns:

ID (Primary key)
Date (Date/Time)
Train no (short text)

I have designed a form where date can be selected from a combo box and according to the date selected, respective trains should be displayed.

Problem is, the query works fine on dates above 10 (like 11/1/2015) but below 10 (like 9/1/2015) it gives error : "No current record" . The record is there in the table but it doesn't display.

The query is : SELECT DISTINCT [Train No] FROM Issue WHERE [Date] = #" & dt & "#"

dt is the date selected from the combo box.

Gustav · Accepted Answer · 2015-07-18 08:30:48Z

4

Try this:

Dim DateSelected As Date
Dim DateString   As String

DateSelected = DateValue(Me!YourComboBox.Value)
DateString = Format(DateSelected, "yyyy\/mm\/dd")

SELECT DISTINCT [Train No] FROM Issue WHERE [Date] = #" & DateString & "#"

answered Jul 18, 2015 at 8:30

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

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

5 Comments

Pragya Dalal Over a year ago

Thanks :) It worked. Can u please explain why it takes only this format?

Gustav Over a year ago

In SQL, string expressions for date values must be formatted as yyyy-mm-dd or mm-dd-yyyy (or slashes as separator). As yyyy-mm-dd also is the only reliable format for ADO, stick to the ISO format yyyy-mm-ss as a general rule.

Pragya Dalal Over a year ago

But why then it was working for dates above 10 (like 11/1/2015) ? Does it parse differently for those formats?

Gustav Over a year ago

I guess that if you Debug.Print your old SQL string, that would show why.

Pragya Dalal Over a year ago

There is just a change of date format. Thanks for your time.

Collectives™ on Stack Overflow

MS Access Date not working in VBA SQL query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related