Report Calendar of Events

[MsLady]

PLEASE HELP: Report-Calendar of Events

I am creating a calendar of events.
the fields in my table includes
Date
TimeStart
TimeEnd
EventDescription

I want events to display by the day of the week.
I have customized columns headers for Monday, Tuesday, Wednesday... etc. even though i don't have fields that says for them.

I want the events on Monday to display on the textbox i designated for monday (i.e under the monday column). Tuesday likewise.. and so on.

Please note that i only have the date field... that accepts (e.g 06/14/04)
i don't have fields for dayofweek. I have tried sorting by day but it doesn't produce the result i want.

Is there a way that i can program that if [date] is Monday, display on this textbox and blah blah.

I put this on my control source: It doesn't work.
=IIf((Format([Date],"dddd",0)="Monday"),[EventDescription],"")

I thot with this, i might get the "dayofweek" from formatting the date, ...and if [date] is monday.. display EventDescription. but then, i get "#Error".

Please help me.

 

[smercer]

Is it better for you if you use the calendar part in outlook? Or do you need to have the links and stuff that goes with it?

I would really like to see if it really does work, I want to do it myself.

Please send me a copy of your database and I will have a play with it. email me at:

smercer(replace with @ sympol)tsn.cc

(i did it like this to keep my email address from spam bots)

 

[MsLady]

Hi smercer,
I have a calendar that pops up when the user clicks the text box, and when they select the date-it fills the textbox with the date the user selects from the calendar. It's a calendar VB program i downloaded online.

I will email you my dbatase soon as i get a chance today perhaps u can take a look, or if u have a suggestion.

My major problem is:

I want my report to work in such a way that, e.g If the date entered falls on a Monday, display records for monday here.. and Tuesday display records for tuesday here..so on.

I entered this on the control source for Monday. It doesn't work.

=IIf((Format$([DateOfEvent],"dddd",0)="Monday"),[EventDescription],"")
with this i got "#Error"

I also tried this
=IIf(DatePart("w",[DateOfEvent])=2,[EventDescription],"")
I get no records. Just i get a blank output.

Please help needed.

Desperate user
MsLady

 

[pdx_man]

Try the Weekday Function:

IIf WeekDay([DateOfEvent]) = 1,...

Also, I see here that you are referencing [Date]. Please know that Date is a reserved word to return the system date and should never be used as a fieldname. If you do have a field with this name, I highly suggest changing it immediately before any more code gets written referencing it.

 

[mr moe]

Can you attach a copy of the database I'm looking for almost the same thing.

 

[Rich]

You can display the day from any entered date by just setting the format of the control to dddd or using the Format Function to do the same, I would use a crosstab query to base the Rport on too

 

[smercer]

I tried this in my own database and I got the day of the week, like you wanted.

Format([Todays_Date],"dddd")

notice there is no "0" at the end like you had?

by the way the "[Todays_Date]" is another unbound text box with the formula :

=Date()

in it. may be if you try puting in a few extra unbound textboxes(even if you do not want them to show) just to see if you get it working. if you do not want them to show when (and if) you get it working you can always hide them and you still get the result what you wanted.

don't forget to email me that program. thanks

 

[smercer]

Oops,

I overlooked part of your code. You do have another text box. sorry

 

[MsLady]

Try the Weekday Function:

IIf WeekDay([DateOfEvent]) = 1,...

Also, I see here that you are referencing [Date]. Please know that Date is a reserved word to return the system date and should never be used as a fieldname. If you do have a field with this name, I highly suggest changing it immediately before any more code gets written referencing it.

ohh yippie!!! this works!!!

=IIf(WeekDay([DateOfEvent])=1,[EventDescription],"")

i know what you mean by using date, I've stayed away from using it. I meant to write DateOfEvent. Thank you much.

 

[MsLady]

I tried this in my own database and I got the day of the week, like you wanted.

Format([Todays_Date],"dddd")

notice there is no "0" at the end like you had?

by the way the "[Todays_Date]" is another unbound text box with the formula :

=Date()

in it. may be if you try puting in a few extra unbound textboxes(even if you do not want them to show) just to see if you get it working. if you do not want them to show when (and if) you get it working you can always hide them and you still get the result what you wanted.

don't forget to email me that program. thanks

Smercer, oh i see what you talking about... Format([Todays_Date],"dddd")without the zero "0", but then i tried that. putting one more zero, and taking away all zeros. I spent almost 7 hours on it .. i don't know what it was i was doing wrong.

=IIf((Format$([DateOfEvent],"dddd",0)="Monday"),[EventDescription],"")
and
IIf((Format$([DateOfEvent],"dddd")="Monday"),[EventDescription],"")
then, i also tried taking away the dollar sign, but it won't take it.. it deems it as syntax #error.
IIf((Format([DateOfEvent],"dddd",0)="Monday"),[EventDescription],"")

anyhow. Im just soo happy, i got that working. It was drving me nuts.

There's one more problem though, ..Each record prints on seperate pages.now i have gazillion pages of each record
LOL.. i will be back with full explanation, perhaps you know how to do this.

Do you still need my database? i was soo busy yesterday.

Thanks again for help.

 

[MsLady]

Can you attach a copy of the database I'm looking for almost the same thing.

attach where? to an email or to the forum if we are allowed to do that??

 

[MsLady]

You can display the day from any entered date by just setting the format of the control to dddd or using the Format Function to do the same, I would use a crosstab query to base the Rport on too

why do you suggest crosstab?? Please let me know.
Whatever will make my life easier

I am not really familiar with the crosstab query.. but let me know how it works and the advantages, im sure i can get around with it.

Thankss.


Thanks again to all that took the time to reply. Yall saved my life.
I still have one mor ething. Perhaps i need a new thread for this.
But you all know the deal with my psyco calendar of events.

see, I grouped by week.
All i want all records for monday to stay under that monday column, tuesday likewise etc. I have been able to get it that way (for each to stay in designated fields) - thanks to you guys. Only the records won't stay on the same page.

Like i have one record for monday display here.
The next record .. also for monday, in same week (even same date), should display after the previous one (row) right?, but it goes to the next page.
So i have many pages.. for each record. What do you think?

 

[WayneRyan]

Hi Ms,

Would love to help, but must see a Database.

You can attach one by:

Remove all graphics and non-pertinent stuff,
Tools --> Database Utilities --> Compact/Repair
Then zip into a file.

You can attach it by scrolling down the screen when you post.

Wayne

 

[MsLady]

Hi Ms,

Would love to help, but must see a Database.

You can attach one by:

Remove all graphics and non-pertinent stuff,
Tools --> Database Utilities --> Compact/Repair
Then zip into a file.

You can attach it by scrolling down the screen when you post.

Wayne

If you take a look at the report titled "Report for Weekly Calendar of Events by Room", you'd see where i stopped...Why does each record print on seperate rows, then i have blank columns. How can i have all my data print from left to right.

I based the report on the select query titled "QueryforReport".

Someone suggested i use a crosstab, but it doesn't work for me. you can take a look (at title: "RequestTable_Crosstab" and please tell me what you think.

Thanks you sooo much.

 

[MsLady]

huh i just attached the zip database. it's not showing.. i think?
lemme try this one more time.

 

[MsLady]

here is a sample layout.
i need something similar of close to how this looks. any suggestions please.
God will repay you in multiple folds.

 

[Pat Hartman]

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, by special request, I posted it. If it has a problem, it is probably caused by a reference issue.

 

[smercer]

Hi MsLady

thanks for emailing it to me.

after looking at the database, I suggest that you have a tables relationship, one for classroom and another for furniture. If you like I can do it for you, but you know your database and I would have to thoughly look through your database to do major work to it

also pdx_man stated that using "date" as a field could cause problems, although I did not know this. you are also using "Time" as a field. could I ask pdx_man to confirm this: would this conflict too?

One other thing: you have spaces in your fields. It is good practice to leave out spaces and format your field like this:
Instead of:

Record Number

use:
Record_Number
this is using the underscore key (Press the shift and press the hypen key which is next to the plus key)

or

RecordNumber

the reason for this is when you use the macros or VBA (Visual Basic for Applications) you will have a hard time coding. even though you have not used the VBA directly you are using the wizard to create buttons and such, creates the VBA for you (So you are using VBA indirectly). It may cause errors but I can't remember where I have had an error from this (This is what my teacher told me).

also I think the best way for you to do your report is to possibly create a pivotChart or table (I am guessing here) in the query and when your report is open, in the database window drag the query over to the report window.

to create a pivotChart just click on the view menu and select pivotChart/pivotTable which ever goes well with you.

adjust the fields (You click on the field header and drag them) along the top of the Chart/table or along the left or right of the table (where ever you want)

I have not got very far with your data base because my printer is offline and having problems with that and am geting another problem possibly from a broken link that was linked to your computer.

 

[smercer]

Opps,
Forgot to mention: you have spaces in your table, query, form, reports names as well. take these out in the same fashion.

 

[MsLady]

Hi MsLady

thanks for emailing it to me.

after looking at the database, I suggest that you have a tables relationship, one for classroom and another for furniture. If you like I can do it for you, but you know your database and I would have to thoughly look through your database to do major work to it

also pdx_man stated that using "date" as a field could cause problems, although I did not know this. you are also using "Time" as a field. could I ask pdx_man to confirm this: would this conflict too?

One other thing: you have spaces in your fields. It is good practice to leave out spaces and format your field like this:
Instead of:

Record Number

use:
Record_Number
this is using the underscore key (Press the shift and press the hypen key which is next to the plus key)

or

RecordNumber

the reason for this is when you use the macros or VBA (Visual Basic for Applications) you will have a hard time coding. even though you have not used the VBA directly you are using the wizard to create buttons and such, creates the VBA for you (So you are using VBA indirectly). It may cause errors but I can't remember where I have had an error from this (This is what my teacher told me).

also I think the best way for you to do your report is to possibly create a pivotChart or table (I am guessing here) in the query and when your report is open, in the database window drag the query over to the report window.

to create a pivotChart just click on the view menu and select pivotChart/pivotTable which ever goes well with you.

adjust the fields (You click on the field header and drag them) along the top of the Chart/table or along the left or right of the table (where ever you want)

I have not got very far with your data base because my printer is offline and having problems with that and am geting another problem possibly from a broken link that was linked to your computer.

thank you soo much for that detailed explanation and suggestions.
and thanks for taking the time to look. you are the best.
I know what you mean about the [Time], [Date] and spaces_between_my_fieldnames.
Somebody else started on this, and pretty much dumped a messy db on me.
I have been doing alot of housekeeping around this database. I left the date and time since the previous person based alot of her queries, reports and the form on the table. I thought if i tried to change any fieldname, it might not link to the appropriate places. dunno .. perhaps it will?
or i have to go to each control in the db and change it... that'd take time. I was just trying to get my report situated.
But i guess it's better to take the time now, than get frustrated later when i could have saved myself the pain.

Well, i will start working on this with your suggestions. I will let you know how it goes.
I will have to print your reply to follow closely cos i'm not familiar wit the pivottable. I will also consider creating a classroom table.

Many thanks, may God bless you.