Multi tool use

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

1 Answer
1

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") & "#"


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.