Retrieve record for specific date which is stored with Effective and Inactive dates

[Marnhullman]

I have a table that stores records each with two date fields - an Effective (start) date and Inactive (end) date.

How do I retrieve a record for a specific date e.g. with a record that has a Start date of 01/03/2023 and an End date of 10/04/2023 how do I retrieve the record for 23/03/2023?

Also how would I retrieve multiple records for a range of dates e.g. a record for each day between 06/03/2023 and 05/04/2023? I can generate a table with the required date range but can't figure out how to join or use it to run a query that will produce a record for each date in the range.



Thanks

[CJ_London]

Your criteria would be something like

where start date<=[enter date] and enddate >=[enter date]

depends wether you want to include records that start or end on the required date as to using the =

and depends on how you are doing this - if using msgbox or creating a query in vba you will need to format the date string and surround with the # character

[June7]

You want to retrieve any records where a given date falls between Effective and Inactive?

WHERE [enter given date] BETWEEN Effective AND Inactive

BETWEEN AND is inclusive of range ends

You show date values in non-U.S. format - that could be an issue. Review http://allenbrowne.com/ser-36.html

[Marnhullman]

Thanks. I think I've tried your suggestions amongst the various options I've used but will give it a go later.

Regards

[Marnhullman]

Thank you June7 your answer works!