Best way to create a Report like this

cdoyle · Feb 28, 2006

Hi,
I'm working a database to keep track of employee's days off, vacation ect.

Someone asked if a report could be created similar to what I have attached.

My main table is pretty simple, it consists of:
Employee Name
Date Off
Department
ReasonCode (reason for day off)

Would it be possible to create a calendar type report, with the table structure I have now?

thanks
chris.

MStef · Mar 7, 2006

Hello Chris!

If this matter is still important, look at "DemoCrostabRptA2000.mdb".
Look at Table1. There are 31 DUMMY records, (with "aa" in EmplName).
Don't delete this records.
Open Form1, select a manth (2, 3 or 4) and click on command button.
Maybe you have to adapt something. My system date is "dd.MM.yyyy"

MStef · Mar 7, 2006

Only "Query1" is important, ignore the other.

cdoyle · Mar 7, 2006

Thanks!

I'm looking at the form, and when I select a month and hit preview, I get an error box that says

The microsoft Jet Engine does not recognize " as a valid name or expreession.3070

I noticed on the top of the form, this note
MICROSOFT DAO 3.6. OBJECT LIBRARY in References.

what does this mean? could this be causing the error?

MStef · Mar 7, 2006

Open Form VBA, Click on Tools, References, find this lib and select them (if it
is not selected yet).

cdoyle · Mar 7, 2006

OK, just checked and it's checked.

but the form won't preview the report.
I can't seem to get it to work, any idea what I'm doing wrong?

MStef · Mar 8, 2006

I think you have to adapt the VBA code in Form1, Command38_Click,
line rst("DateOff") = Format(DatePart("d".........
depending on your SYSTEM DATE format. Or set your SYSTEM DATE format
on dd.MM.yyyy format and tray.

cdoyle · Mar 8, 2006

OK, our systems are set to mm/dd/yyyy
What do I need to change in the code, to make it work with this type?

thanks again
Chris.

MStef · Mar 8, 2006

Hello Chris!

Here it is a new one.

cdoyle · Mar 8, 2006

Hi,
Just tried the new one, I noticed you added the drop down for the month. So I gave it a try, and it's still doesn't want to work for me.

I stil get that
'The microsoft Jet database engine does not recognize " as a valid field name or expression.3070

Not sure what it's looking for, or how to fix it. any ideas?

I really want to see how the report looks, it looks good in design view

MStef · Mar 9, 2006

It seems to me thet your SYSTEM DATE format isn't mm/dd/yyyy.

cdoyle · Mar 9, 2006

The way I'm checking is,
going to start>run> type cmd and I type in Date /t
it displays
03/09/2006

is there another way to check?

MStef · Mar 9, 2006

I'm so sorry, I don't know what is the problem with your PC.
On my PC it works without any problem.

cdoyle · Mar 9, 2006

Can anyone else give it a try, and let me know if it works for them? I'm not what to do, to make it look.

MStef · Mar 10, 2006

Hello Cdoyle!

Stil one thing I doubt, try to change ; with , in Query1 (Expr1: and DatePart column).

Fear Naught · Mar 10, 2006

I get the same problem. I can run the query without any problem but not the report!

cdoyle · Mar 10, 2006

hmm,
I'm looking at query1 and I don't see a Expr1. There is an expr2: Reason code.

Here is the SQL

PARAMETERS [Forms]![Form1]![T39] Short;
TRANSFORM First(Table1.ReasonCode) AS Expr2
SELECT Table1.EmplName
FROM Table1
WHERE (((DatePart("m",[DateOff]))=[Forms]![Form1]![T39]))
GROUP BY Table1.EmplName
ORDER BY DatePart("d",[DateOff])
PIVOT DatePart("d",[DateOff]);

cdoyle · Mar 13, 2006

Is there anything else I can do to make it work?

cdoyle · Mar 13, 2006

OK, been messing around with the query.

I removed the [Forms]![Form1]![T39] prompt, so the query would run. I noticed that day 2,5,7,8, and 9 Don't appear in the query (because nothing has been entered for those days.)

so when I try and make a report based off this query, I get a

The microsoft jet database engine does not recoginize "[2]" as a valid field name or expression

so on the report I removed all those fields and it worked. Is there anyways to make this work, if no data for that day has been entered?

cdoyle · Mar 17, 2006

Is there anyway to make the report work when there might be days with no entries?

It works, if I put an entry in for every day of the month, but it wont' work if there is 1 day with no entries.

Best way to create a Report like this

Registered User.

Attachments

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Kevin

Registered User.

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread