Date Range within a Date Range

[KyleB]

I'm trying to figure out how I can set a parameter on my query to search for any activity that occurs within a month, that falls within a date range. Specifically:

Criteria TextBox: 4/2006

Activity Start: 3/6/2006
Activity End: 5/5/2006

I want this activity to show in my query because the time between start and end occurs during the month of April. It's no problem setting this up for a specific date, ie 4/1/2006, but I don't know how to do it for an activity that occurs all days between 4/1/2006 and 4/30/2006..or for one that starts 4/12/2006 and ends 4/18/2006, or starts 4/12/2006 and ends 5/9/2006. etc.

 

[SamDeMan]

between month(ActivityStart) and Month(ActivityEnd)

you may want to use one of the following functions:
dateserial(Year(activityStart),Month(activityStart),1)
format(ActivityStart,"mmyyyy")
it all depends how criteria textbox is formated.

good luck,

sam

 

[Jon K]

The criteria for a Date Range within a Date Range is:-
ActivityStart <= LastDayOfCriteriaRange and ActivityEnd >= FirstDayOfCriteriaRange

For the month of 4/2006, it is:-
ActivityStart <= #4/30/2006# and ActivityEnd >= #4/1/2006#


The attachment shows how you can use the DateSerial() function in the query criteria to transform the month of 4/2006 entered in the text box on the form to LastDayOfCriteriaMonth and FirstDayOfCriteriaMonth for the query.

You can open the form, type a month e.g. 4/2006 and click on the command button to run the query "Query1".
.