I am creating an employee attendance database. It will be used by Managers to
log employee vacation time. Entering individual days is not a problem, but if
an employee is taking two weeks, is there a way to enter the range and have
access create records for each date within the range. The current form layout
is as follows, Corp ID, Last Name, Date, Reason Code. For example, an
employee is taking 1/01/07 to 1/15/07. Is there a way to enter this info with
having to do it individually. And of course, exclude weekends.

Create a table containing a record for each work date. Just one date/time
field named (say) TheDate, marked as primary key, and save the table as
tblDate.

You can now create an Append query statement that selects the days in the
range the person will be away, and appends them to your table of absences.

If you need help getting the SQL statement to execute, mock up a query,
change it to an Append query (Append on Query menu), type in any literal
employee in the Field row. Once you have mapped the fields, you can switch
to SQL View (View menu) to see an example of the string you need to create.

Ultimately, the code will be:
Dim strSql As String
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Vinny P" <(E-Mail Removed)> wrote in message
news:F535EE3F-FE8F-4068-AA04-(E-Mail Removed)...
>I am creating an employee attendance database. It will be used by Managers
>to
> log employee vacation time. Entering individual days is not a problem, but
> if
> an employee is taking two weeks, is there a way to enter the range and
> have
> access create records for each date within the range. The current form
> layout
> is as follows, Corp ID, Last Name, Date, Reason Code. For example, an
> employee is taking 1/01/07 to 1/15/07. Is there a way to enter this info
> with
> having to do it individually. And of course, exclude weekends.

On Mon, 22 Jan 2007 11:26:02 -0800, Vinny P
<(E-Mail Removed)> wrote:

>Sorry if I am being confusing, but unfortunately, I am a bit confused. I
>understand building a table to eliminate weekends and holidays. But I still
>cant visualize tying the queried dates into my table. Here is an example of
>my table:
>
>Tbl_Attendance_Tally
>Corp_ID
>Last_Name
>Date
>Day_Code (ie:vacation, sick, personal)
>
>For every record, this information is required.. If I build a form to
>accomodate multiple date input, for example, From_Date and _End_Date and
>query the business day table to retrieve all potential business days in the
>range entered, how do I create individual records for each date. Last
>example, I am taking off 2/1/07 to 2/14/07 and they are all vacation days. I
>want the ability to enter this once. I am sorry if I am repeating myself, I
>just want to make sure I am clear.

PMFJI... I see Allen hasn't had a chance to respond.

I think what he had in mind is that you would create an Append query,
drawing the date from this table of business days and the other fields
from your Form; e.g.

INSERT INTO tblAttendanceTally(Corp_ID, Last_Name, [Date], Day_Code)
SELECT [Forms]![YourFormName]![cboCorp_ID],
[Forms]![YourFormName]![txtLast_Name], tblWorkdays.WorkDate,
[Forms]![YourFormName]![cboDay_Code] FROM tblWorkdays
WHERE WorkDate BETWEEN [Forms]![YourFormName]![From_Date] AND
[Forms]![YourFormName]![To_Date];

A couple of suggestions: Last_Name is NOT suitable, unless you refuse
to hire two people who happen to have the same name. I know three men
named Fred Brown and once worked at the same university as Prof. John
Vinson. Use a unique EmployeeID instead. And, don't use Date as a
fieldname - it's a reserved word for the builtin Date() field.

John W. Vinson[MVP]

It worked.. Thank you both for all your help!!

"John Vinson" wrote:

> On Mon, 22 Jan 2007 11:26:02 -0800, Vinny P
> <(E-Mail Removed)> wrote:
>
> >Sorry if I am being confusing, but unfortunately, I am a bit confused. I
> >understand building a table to eliminate weekends and holidays. But I still
> >cant visualize tying the queried dates into my table. Here is an example of
> >my table:
> >
> >Tbl_Attendance_Tally
> >Corp_ID
> >Last_Name
> >Date
> >Day_Code (ie:vacation, sick, personal)
> >
> >For every record, this information is required.. If I build a form to
> >accomodate multiple date input, for example, From_Date and _End_Date and
> >query the business day table to retrieve all potential business days in the
> >range entered, how do I create individual records for each date. Last
> >example, I am taking off 2/1/07 to 2/14/07 and they are all vacation days. I
> >want the ability to enter this once. I am sorry if I am repeating myself, I
> >just want to make sure I am clear.
>
> PMFJI... I see Allen hasn't had a chance to respond.
>
> I think what he had in mind is that you would create an Append query,
> drawing the date from this table of business days and the other fields
> from your Form; e.g.
>
> INSERT INTO tblAttendanceTally(Corp_ID, Last_Name, [Date], Day_Code)
> SELECT [Forms]![YourFormName]![cboCorp_ID],
> [Forms]![YourFormName]![txtLast_Name], tblWorkdays.WorkDate,
> [Forms]![YourFormName]![cboDay_Code] FROM tblWorkdays
> WHERE WorkDate BETWEEN [Forms]![YourFormName]![From_Date] AND
> [Forms]![YourFormName]![To_Date];
>
> A couple of suggestions: Last_Name is NOT suitable, unless you refuse
> to hire two people who happen to have the same name. I know three men
> named Fred Brown and once worked at the same university as Prof. John
> Vinson. Use a unique EmployeeID instead. And, don't use Date as a
> fieldname - it's a reserved word for the builtin Date() field.
>
> John W. Vinson[MVP]
>