CODEWITHSUNDEEP
sqlvbams-access
Juan
Juan

Reputation: 2084

How to compare dates in Access VBA?

I have an Access database with two date/hour fields "record_init_date" and "record_end_date".

I have a query to retrieve the records with record_init_date >= init_date and record_end_date <= end_date like this:

"SELECT * FROM TABLE WHERE (record_init_date >= #" & Me.selectorInitDate.Value & "#) AND (" & record_end_date & "<= #" & Me.selectorEndDate.Value & "#)"

But the result is not the desired one. How can I filter by date?

EDIT

Image some sample data like this:

id = 1
record_init_date = 10/11/2018
record_end_date = 20/11/2018

id = 2
record_init_date = 03/12/2018
record_end_date = 04/12/2018

If I run the query:

"SELECT ID FROM TABLE WHERE (record_init_date >= #01/11/2018#) AND (record_end_date <= #30/11/2018#)"

The expected result is the ID = 1

Upvotes: 0

Views: 2276

Answers (1)

Gustav
Gustav

Reputation: 55806

You probably need to apply a format to obtain a date expression:

"SELECT * FROM TABLE WHERE record_init_date >= #" & Format(Me.selectorInitDate.Value, "yyyy\/mm\/dd") & "# AND record_end_date <= #" & Format(Me.selectorEndDate.Value, "yyyy\/mm\/dd") & "#"

Upvotes: 1

Related Questions