Date range in specific month query

[Sck]

I thought this would be easier, but I just can't work it out.



I have a table with records that have a Start Date and End Date fields. I need to pull all records that happen at some point during a specific month.

Example: Need all records that span some part of March 2022

Results sample:
Record 1 Start 2/25/22 and end 3/1/22
Record 2 Start 3/28/22 and end 4/15/22
Record 3 Start 2/25/22 and end 4/10/22
Record 4 Start 3/5/22 and end 3/11/22

I could write a VBA function to loop through the date range and if month value for any of the dates is March (3) and year value is 2022 then say true and include it but I have found functions like this are very slow to produce query results.

Any thoughts? I really think I am making it harder than it needs to be, but I am just not seeing it.

[ranman256]

functions are slow, so Cant you make a form and put 2 textboxes on it txtStartDate, txtEndDate

select * from table where Between [Forms]![fDateRng]![txtStartDate] And [Forms]![fDateRng]![txtEndDate]

or even use just the year
...where year([dateFld)) = [Forms]![fDateRng]![txtYear]

[Sck]

functions are slow, so Cant you make a form and put 2 textboxes on it txtStartDate, txtEndDate

select * from table where Between [Forms]![fDateRng]![txtStartDate] And [Forms]![fDateRng]![txtEndDate]

or even use just the year
...where year([dateFld)) = [Forms]![fDateRng]![txtYear]

I could but that may not get everything. if the record start date is 2/28/22 and end date is 4/1/22 and the person selected March (3/1 to 3/31) the record would not be included My issue is I need to pull the record if any day between the start and end is in March, I need the record. Just looking at start and end is not getting me there (I don't think)

[Gicu]

Review this thread and you should have your answer:
https://stackoverflow.com/questions/...ranges-overlap
Cheers,

[Sck]

think i found it in another post on the forum The following seems to work. THANKS

(StartDate1 <= EndDate2) and (EndDate1 >= StartDate2)

[Sck]

Thanks Gicu found the same post they were a lot more detailed than I needed for this application as I know the start and end dates in both ranges will be fully populated and in the correct order (starts < ends)

[Gicu]

[canyonraven]

Hi Sck,

I am trying to understand your problem using set theory. You have a "set of records" with start and end dates. If I understand it right, you want to filter them using a "timeline" which is a conditional "WHERE" clause. Is that right?

[Sck]

correct basically trying to find any records where the start and end dates overlap in any way the start and end of a specific month