Date ranges using Calendar control

[LilMissAttack]

Good morning,

I have a form for users to enter scheduled time off. I am using the calendar control, two text boxes, one where they enter the start and another for the end date.



I need a subreport to be able to read the dates in between...for instance if their start date is 8/5/2011 and end date 8/10/2011, I want them to appear on the appropriate reports (8/5, 8/6, 8/7 etc).

Has anyone done something similar? Could I do some sort of expression in the query that reads those dates in between?

Thanks!

[rpeare]

Are you trying to get a list of all dates between a certain range? i.e. if they schedule a week off you want to list the start date the end date and all the dates in between? OR do you just want to query a date field using the starting and ending points of the requested vacation period?

If it's the former I don't know that you can do it without VBA code or appending data to a table. If it's this option I'm assuming you want to append a record for each vacation day to a days off table with a notation of what type of time off it was?

If it's the latter just use the between function in the criteria of your date field, just remember that date fields on forms follow the format forms!formname!fieldname and date fields have to have a # sign at the front and end of the string.

[LilMissAttack]

Thanks for your reply. Yes, I want to do exactly what you mentioned first.. getting a record for the start and end dates and all between. Appending to a table would be perfect...I hadn't thought of that....so you think this will have to be done through VBA? I'm trying to think if there are any other ways to do that.....

[rpeare]

Well what is your end goal? if you can give me some idea as to what you want to do with this list or what it's for it'd be easier to give you a suggestion.

[LilMissAttack]

My subreports are looking at queries by date and pulling the employees name and their other data into the report.

So users could be entering just one date, (ex. Start Date = 8/1/2011, End Date 8/1/2011) Or they could put a range(ex. 8/1/2011 - 8/10/11)
So I need it to look and then create a record for 8/2, 8/3, 8/4 and so on....

[rpeare]

Hang on

Let's say you have a person who has put in for vaction from 1/1/2011 to 1/14/2011. Now when you do your search and you want to see all the people who have time off between say 1/5/2011 and 1/12/2011. You would want this person to appear? If that's the case you don't need to generate a list of dates to accomplish this you just have to change your criteria


Let's say you have a search range of 1/7/2011 through 1/14/2011, a one week span.

Now let's say you have a timeoffstart and timeoffend field in your table

there are four possible matches for someone who has time off during this span

1. The person's first date off is prior to the search start AND the last date off is after the search end date.
2. The person's first date off AND the person's last date off are BOTH within the search range
3. The person's start date is in the search range and the end date is AFTER the search end date
4. The person's start date is prior to the search start date AND the end date is in the search range

You just have to provide for all four possibilities in your query you would basically have four lines in your criteria (the first of the alternate criteria is labeled OR and anything below that is considered an OR as well)

So if you were hard coding the dates (mentioned above) into your query where your two fields were StartDate and EndDate

In the first row of your criteria you would have

<=#1/7/2011# for STARTDATE
>=#1/14/2011# for ENDDATE

in the second row of your criteria you would have

>=#1/7/2011# for STARTDATE
<=#1/14/2011# for ENDDATE

for the third row your criteria you would have

BETWEEN #1/7/2011# AND #1/14/2011# for STARTDATE

for the fourth row your criteria you would have

BETWEEN #1/7/2011# and #1/14/2011# for ENDDATE

[LilMissAttack]

I see! OK so my queries are only looking for that specific date. For instance, I have a query specifically for 8/15/2011. It needs to find any records where the start date or end date is equal to 8/15/2011, or where 8/15/2011 falls within the range they entered. I won't always know what the range is...does that make sense? Sorry for my lack of knowledge on this subject....

[rpeare]

it doesn't matter if you know what the date range will be, you would just substitute your 'search dates' wherever you see a date in my criteria, whether your user will be prompted to enter a variable or you will take it from a from is up to you, the method will work you just have to apply it to your situation.

[LilMissAttack]

yES!! Got it by following what you gave me above. Thank you so much!