Need Help in formatting a report

[Guest]

I have a db that tracks employee time off.

Simple table with these columns
Employee Name
Date Off
Department
ReasonCode

What I would like is to create a report that will display each employee's
time off for the month, in a format like this
1/1 1/2 1/3 ect
Employee #1 PTO
Employee #2 Pto

I've posted this question on some other forums, and on one I got a response
with a sample database, and report. but I can't seem to get the report to
work correctly.
It only seems to work if there is an entry for every day of the month. If
there is 1 day with no entries, it won't run.

I've tried getting help , but nobody has answered. So I'm hoping that
somone might be able to help.

Here is a link to the thread I posted on teh other forum, that has the
sample db.

http://www.access-programmers.co.uk/forums/showthread.php?t=103014

Or if anyone else has a better way of creating the report, please let me know.

Thanks

 

[Anonymous]

You and I are doing almost the exact same thing, although I'm trying to
put employees on the top (Column Headings) and dates on the side (Row
Headings), and I'm collecting two dates, startdate and enddate. Amazing.

I take it the columns are supposed to go from, say, 1/1/06 (Jan1/06) to
1/31/06 (Jan31/06)?

Maybe we can work together...

Jeff

 

[Anonymous]

In the database you provided the link for, in the other thread, generated
an error due I think to trying to stick 01.03.2006 (a non-standard date
format, not recognized as an actual date by Access) into a date field. I
didn't want to spend much time on this part, because it's not actually
addressing your question...

Jeff

 

[Anonymous]

Ok so I spent more time on it as a distraction from MY issue...

The line that is causing the error should read:

rst("DateOff") = DateSerial(DatePart("yyyy", rst("DateOff")),
Format(Forms!Form1!T39, "00"), Format(DatePart("d", rst("DateOff")), "00"))

Change that one line, and the report previews fine, EXCEPT for any month
that doesn't have 31 days, because the only way to break out of the
do...loop is to reach 31. Try the 2nd month (February), and the code runs
forever, till you stop it with CNTRL+BREAK:

Do
rst.Edit
rst("DateOff") = DateSerial(DatePart("yyyy", rst("DateOff")),
Format(Forms!Form1!T39, "00"), Format(DatePart("d", rst("DateOff")), "00"))
rst.Update
If DatePart("d", rst("DateOff")) = 31 Then
Exit Do
End If
rst.FindNext strCriteria
Loop


Jeff

 

[Anonymous]

Hmm...so it only runs fine the 1ST time ya run it...

Why it's coded to change the DateOff dates I don't know. You'd have to ask
the author.

Jeff
PS- At least I fixed the error message...

 

[Guest]

Anyone?

 

[Guest]

Chris,
Try creating a "Crosstab" query report using the same fields that you have.
Use the "Date Off" field as your column.

 

[Guest]

Hi,
I had tried that, but it didn't work.
Intead of listing the 'reason' code under each date. It tries to sum them.

Any other ideas,
The example that was given too me used a crosstab query, just had some
expressions to make it work. The biggest problem is the report, it won't
work if there are any days with no data.

 

[Anonymous]

No comment on my input, then?

Jeff

Hi,
I had tried that, but it didn't work.
Intead of listing the 'reason' code under each date. It tries to sum
them.

Any other ideas,
The example that was given too me used a crosstab query, just had some
expressions to make it work. The biggest problem is the report, it won't
work if there are any days with no data.

 

[Guest]

Is it not possible to format a report in the matter I need?