Create List of dates and information

[James Stephens]

I am stuck with a problem and could use some help. I am pretty good with
access but this one I just can't think through to completion.

Here is the situation I am stuck in. I have built a calendar form to
display leave information for our scheduling officer. This uses 42 command
buttons and 42 lables. It is filled with information from a table. The table
that stores the leave information has 5 fields:

LeaveStartDate , LeaveEndDate , Approved , Denied , EmployeeID

The "Approved" and "Denied" are yes/no fields.

What I need to do is creat a query on this table that will generate a result
with three fields: DateCode, Approved, Denied. The calender is already set
to work off of a query with this format.

My only thoughts is to get the start date and end date from the code that
works the callender, this I can do. This for all the dates between these
two, look at the origional table, if this date falls between the two listed
(leavestartdate and leaveenddate) then create a record for that date and if
leave is approved or denied so that it can be totaled and then sent to the
callender. Sorry if this is confusing, here is an example.

LeaveStartDate LeaveEndDate Approved Denied EmployeeID
11/02/2004 11/06/2004 Yes No
1234
11/04/2004 11/07/2004 No Yes
2345

This would need to be interperted and generate a list like this:

DateCode Approved Denied
11/02/2004 1 0
11/03/2004 1 0
11/04/2004 1 0
11/05/2004 1 0
11/06/2004 1 0
11/04/2004 0 1
11/05/2004 0 1
11/06/2004 0 1
11/07/2004 0 1

This way I can then sum this and get a total count of leave approved and
denied on each day that will then show up in the calendar. All of the
calender is done, I just have to find a way to convert this table of
information into the format above. I could really use the help on this one
as I am stuck.

Thanks for any ideas,
James Stephens

p.s. I had look for calender forms and hadn't really found anything, except
for people taking about building one, this one is pretty good if I do say so
myself. The days can be clicked on to bring up more detailed information,
and the months can be moved forward and backward. If anybody would like
this, I am more than willing to give it out as it may help someone save some
time.

 

[[MVP] S.Clark]

My solution would involve VBA to parse out the data from the stored data, to
how you need to see it on the report.

 

[James Stephens]

Could you elaborate some on how to go about this. I am pretty comfortable
with VBA (most of the calender function is based on VBA), but I can't come up
with a way to go about this.

Thanks for any direction you can give me,

James Stephens