Report Calendar of Events

[MsLady]

I made a calendar type report recently. It may be more complicated than you need because it has to use crosstabs and even a function to get some data. But the report has pretty colors and the queries may help you.

Sorry folks, it's in A2K format. If you have a pressing need, I can convert it but since I can't test it, you're on your own.

ok good. thank you so much. I noticed you have cross-tabs too, i'd study that.. i have problems with my crosstab.
Thanks again.

And one thing though, my reports (though in progress), and queries would normally open free of "this error messages", now, i get errors (see attached), does somebody have a clue what is wrong?

It was opening fine at work, then i got home, and tried opening this, but it's giving me daggone errors . with either my "format" or "weekday" funtion. Same errors when i tried with another computer.

Pat, i tried opening your queries too and i got "Undefined funtion 'weekday' name in expression" see attached (even though i forced it to open but this error still pops up when i tryto run or view it in design view. Im sure it was error-free on your computer.

i don't need this kindda headache right now. does anyone know what could be goin on here.

 

[WayneRyan]

Ms,

That's a References problem. Use the search facility here. It is quite
common.

Wayne

 

[Pat Hartman]

Sorry, that function is probably not available in earlier versions. Replace WeekdayName(Format([ClassDate],"w")) AS ClassWeekDay with Format(whateverdate,"dddd") AS ClassWeekDay

 

[MsLady]

Sorry, that function is probably not available in earlier versions. Replace WeekdayName(Format([ClassDate],"w")) AS ClassWeekDay with Format(whateverdate,"dddd") AS ClassWeekDay

this doesn't work either, i still get the error. oh well..

 

[MsLady]

Ms,

That's a References problem. Use the search facility here. It is quite
common.

Wayne

wayne, where and how? im a rookie in access.

 

[smercer]

One other thing I forgot to mention:

You are using the format function in the query. I think you will have to take this format function out because it won't work in the query, it is better to use it only in forms and reports, not queries.

the only functions that work in queries (guess) are ones that sum, count etc

 

[Pat Hartman]

Functions work everywhere. The Format() function works fine in queries.

My guess is you took my post literally. I was trying to get you to think a little. Following is the EXACT string you need to use.

Format(ClassDate,"dddd") AS ClassWeekDay

I posted the way I did because I wanted you to extrapolate the change just in case I had used the WeekdayName() function in another context.

Typing is not one of my best skills. Left out the first "t" in extrapolate

 

[MsLady]

smercer the reason im using the format inthe query is cos i have field for Time (e.g 8:00)
Time1(for AM/PM)... where the user chooses from a drop down, if you take a look at my form (form1) .. you'd see.
I want the query to collect Time&Time1 and format it to 8:00 AM before it goes to the report just in case i need to do something with that in the future. e.g a user canno't schedule for the same room, in the same date and time. naa mean?

Pat, i know waht you meant, i didn't take you literally. hehe

This is exactly what i have

Format([DateReserved],"w") AS DayOfWeek
Format([Start] & [TimeAM],"Medium Time") AS StartTime
Format([End] & [Time1],"Medium Time") AS EndTime


If i can remember clearly, this was working perfectly before.

smercer, please do me a favor. Please help me check it (run the query) titled "QueryforReport" from the copy you have. If you still have it see if it works on your machine. ?

 

[smercer]

I already tried to run the query and I got an error as I said before. I got a different error when I ran the report as I said before.

See attachments

 

[MsLady]

I already tried to run the query and I got an error as I said before. I got a different error when I ran the report as I said before.

See attachments

oh smercer i think i know why!

I linked it a textbox to a vb calendar i downloaded, so that when the user clicks on the textbox, a calendar popsup where they can choose the date from.

If you go to the form labelled "form1".
There's a textbox for DateReserved.. click on the "Event procedure" to that take off the calendar function.

I'll find a better way to do that so that my application won't go crashing people's computer.. LOL hey im still learning.

 

[smercer]

I can gain access to some reports via the button in the form. but not the report that you want to work.

here are a few tips:
1) when you are making a query and want it to ask the user something, you have the question in two squire brackets "[]" (see attachment for a tutoral on making a parameter query). this is an excellent tutoral because it shows you how to make the query based on what you have in the form on that record.

2) your parameter querys are hard to understand. you want it user friendly and ask the user a question they will understand (in english, not in computer language). I dont mean to be rude but it is very hard to understand.

3) your form design looks good. it is very easy on the eyes and does not cause eye strain (a good thing if you look at it all day)

 

[smercer]

oh smercer i think i know why!

I linked it a textbox to a vb calendar i downloaded, so that when the user clicks on the textbox, a calendar popsup where they can choose the date from.

If you go to the form labelled "form1".
There's a textbox for DateReserved.. click on the "Event procedure" to that take off the calendar function.

I'll find a better way to do that so that my application won't go crashing people's computer.. LOL hey im still learning.

I did what you said and now I get a different error for the query. see attachment

I am still geting the same error for the report

Now as I said before about the format function being in a query, I think it is making things worse. if you want the results to display differently in the form or report you can always use the format function there. (at least try it)

 

[MsLady]

I did what you said and now I get a different error for the query. see attachment

I am still geting the same error for the report

Now as I said before about the format function being in a query, I think it is making things worse. if you want the results to display differently in the form or report you can always use the format function there. (at least try it)

ohh wooww.. u got that too. I swear that thing was working before
i'll go change it. thank you

 

[MsLady]

I can gain access to some reports via the button in the form. but not the report that you want to work.

here are a few tips:
1) when you are making a query and want it to ask the user something, you have the question in two squire brackets "[]" (see attachment for a tutoral on making a parameter query). this is an excellent tutoral because it shows you how to make the query based on what you have in the form on that record.

2) your parameter querys are hard to understand. you want it user friendly and ask the user a question they will understand (in english, not in computer language). I dont mean to be rude but it is very hard to understand.

3) your form design looks good. it is very easy on the eyes and does not cause eye strain (a good thing if you look at it all day)

ok, i see. well taken, thanks again for your time and suggestion.

3) your form design looks good. it is very easy on the eyes and does not cause eye strain (a good thing if you look at it all day)

you can bet im gonna be living on this compliment thanks

 

[MsLady]

smercer, guess what! i just tried it at work now!
It doesnt give me that format error. .. hmnn.. very strange. Everything opens perfectly.

Im using crosstab now.. do you know why this gives me #errors?

TRANSFORM " " & Max(Format([StartTime] & [StartAM],"Medium Time") & " - " & Format([EndTime] & [EndAM],"Medium Time") & " " & [Program]) AS StartEndProgram
SELECT RequestTable.RoomReserved
FROM RequestTable
GROUP BY RequestTable.RoomReserved, RequestTable.StartDate, RequestTable.EndDate
PIVOT RequestTable.DateReserved In ("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday");

The only reason i still kept the format in my queries is cos access only allows one value. That's they only way i can get the fields i want to go as one value. perhaps..dunno.

 

[MsLady]

oh never mind.

This does it!


TRANSFORM Min([Start_Time]) & " - " & Max([End_Time]) & " " & Max([Program]) AS StartEnd
SELECT DatePart("ww",[DateReserved]) AS weeknum, RequestTable.RoomReserved
FROM RequestTable
GROUP BY DatePart("ww",[DateReserved]), RequestTable.RoomReserved
PIVOT Format([DateReserved],"dddd") In ("Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday");

 

[smercer]

smercer, guess what! i just tried it at work now!
It doesnt give me that format error. .. hmnn.. very strange. Everything opens perfectly.

this may be because you may be behind the times and have an older version of office (or very new). I am using Office XP.

Im using crosstab now.. do you know why this gives me #errors?

I have never used crosstab before so I can not help much.

 

[cdoyle]

I made a calendar type report recently. It may be more complicated than you need because it has to use crosstabs and even a function to get some data. But the report has pretty colors and the queries may help you.

Is there anyway to make a DAP from the report you made?

Or is there anyway to construct a calendar type DAP that operates in the same way as that report?

I can't seem to ever get anything to work.

Thanks
Chris.