Is this Possible? - Working Days

[Lori]

I have tables, forms and reports set up in my database that work perfectly to
track vacation days taken. The problem is in order for the report to look
right I have to put in each day separately.

Is it possible to enter a range of days ex: start: 05/13/08 End: 05/31/08
and have the database recognize each of the individual dates and based on my
table of dates (indicating holidays) enter the data into my calendar as
individual dates making allowances for the holidays and weekend?

 

[Michel Walsh]

You either use a table pre-filled with all working days, and use a simple
WHERE clause like:

SELECT workingDay
FROM workingDays
WHERE workingDay BETWEEN startingDateParameter AND endingDateParameter

either still need a table with holidays and week end, plus a driver table,
call it Iotas, one field, iota, with values from 0 to, say, 999, and :

SELECT startingDateParameter + Iota
FROM Iotas
WHERE iota < endingDateParameter
AND startingDateParameter + Iota NOT IN
(SELECT weekEndAndHoliday
FROM weekEndAndHolidayDays
WHERE weekEndAndHoliday BETWEEN startingDateParameter AND
endingDateParameter)




(you can change the NOT IN(SELECT ... ) into an outer join, if it is too
slow).



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

should be:

WHERE iota < endingDateParameter +1 - startingDateParameter

instead of :

WHERE iota < endingDateParameter

 

[Lori]

Okay, I've tried this and I'm not getting the results I'm looking for. What I
need would be an update query which would take the date ranges entered by the
user and convert them to individual dates (ex 5/12 - 5/15 would returner as
5/12, 5/13, 5/14 and 5/15). The sample below only pulls out those individuals
who are scheduled off during the "workday". Maybe I did something wrong
(Probably)

HELP

 

[Michel Walsh]

The query just pull the possible days, in the given interval.

The easiest way to continue would be to create a table out of it, with extra
fields, as required. Once the table is generated, its fields can be updated
as usual.

If you want to create an updatable query, you would have to associate the
actual query with an existing table which will hold the to be updated data,
but I know nothing about that existing table.


Vanderghast, Access MVP

Okay, I've tried this and I'm not getting the results I'm looking for.
What I
need would be an update query which would take the date ranges entered by
the
user and convert them to individual dates (ex 5/12 - 5/15 would returner
as
5/12, 5/13, 5/14 and 5/15). The sample below only pulls out those
individuals
who are scheduled off during the "workday". Maybe I did something wrong
(Probably)

HELP