Creating Range of Dates in a Form

[melb]

I had a table where I entered in days employees had off along with the reason behind their absence. Previously I would manually enter each day and the reason in the table to record the information. Today I created a form to enter this information in using Form Wizard. I was wondering if there is a way to make the form populate numerous days at the same time. If someone is taking a week vacation I would want to be able to enter in the date their leaving and the date they are back and have the form create for instance 7 entries for the individual days that person will be gone along with the same information of why they are not there for each day.

Is this possible? If so, what is the best way of going about doing this?

Thanks

[NTC]

It is possible. It is not recommended as a design concept because those days are calculate-able from the Start and End date fields. One would need to write a loop code to find minimum date then apply a +1 to write it in while doing a compare to the maximum date and repeating until one reaches maximum date.

[melb]

Currently I do not have start and end date fields, just a single date field which is the date they have called off. If I did place a start and end date field, how would I make it populate 7 table entries for the 7 days of the week with the data on each field rather than simply listing for instance 7/11/2015 as start date and 7/18/2015 as the end date.

[melb]

So is it necessary to create a new field in the form? As now it's simply a singular date which I input manually each date an employee misses. So instead of DateMissed it would be BeginDate and EndDate for the range of days that are missed due to a vacation or other circumstance

[ssanfu]

So is it necessary to create a new field in the form?

Just to be clear on terminology: Tables have fields - Forms have controls.

Some things to think about:

You would need a button to execute the code and 4 unbound controls on the form:
1) Some way to select the employee
2) A start date control
3) An end date control
4) A reason control

The code would need to check:
if the EndDate > StartDate.
if both controls have valid dates
if only a date in the StartDate control, should only create one entry (ex. take 1 sick leave day)
if the Reason control has an entry. (is the reason required?)

[melb]

Just to be clear on terminology: Tables have fields - Forms have controls.

Some things to think about:

You would need a button to execute the code and 4 unbound controls on the form:
1) Some way to select the employee
2) A start date control
3) An end date control
4) A reason control

The code would need to check:
if the EndDate > StartDate.
if both controls have valid dates
if only a date in the StartDate control, should only create one entry (ex. take 1 sick leave day)
if the Reason control has an entry. (is the reason required?)

Thanks ssanfu.

As it stands I have a selection box with a list of employees, a date control, and a reason control. So all it was lacking was an end-date control. I put one in and it creates an entry in my table that will say something like:

Name StartDate EndDate HoursMisssed Reason
Adam Jones 3/3/2014 3/10/2014 7 hours Vacation Day Approved in Advance

Rather than the desired result which is:
Adam Jones 3/3/2014 7 hours Vacation Day
Adam Jones 3/4/2014 7 hours Vacation Day
Adam Jones 3/5/2014 7 hours Vacation Day

and so on.

Your post was very helpful, but I'm still a bit ambiguous on how to go about getting entries of individual dates being recorded after entering a StartDate and EndDate. Is there a terminology of what I'm trying to achieve so I can easily look it in the index of a book on Access or online without asking tedious questions here? I've tried "getting date range into individual entries on access" and a few similar searches and they don't seem to achieve what I'm looking for.

Thanks!

[ssanfu]

Here is an example dB. I don't know your table name or your field names, so you will have to edit the code.
The is no error handler, so be warned! Just an example of how the code might look.

(in Access 2010)