Report based on Day Of Week-Display certain records to certain fields

[sisieko]

Conditional report-dependant on Day of week....

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

Now I formatted the report with columsn as follows: Monday, Tuesday, Wednesday.. etc.
I want Events for Monday to print under a textbox I designated for the Monday column.. etc.
i.e. 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],"")

Please help.

 

[dhookom]

You don't have a field named [DateOfEvent]. Also, "It doesn't work" doesn't work for us to tell you what is wrong. This just isn't nearly enough information.

If you want to print calendar type reports, check out the samples at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sisieko]

I am sorry, i missed that.
I have DateOfEvent.


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.

 

[dhookom]

I don't know why your expression doesn't work but I would simplify it to:
=IIf(WeekDay([DateOfEvent])=1,[EventDescription],"")
Also, make sure the name of the control is not the name of a field.

I wouldn't expect the layout of this report to be too published looking. Did you review the sample reports in the link I provided?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sisieko]

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

ohh ok. this works!!!

ps: I checked out the link you provided, but it only requires that i download. Anways, i have what i want now, thanks Duane.

One more thing i've noticed though.

I set the headers to group by week. (and of course to keep groups together).
But i have each record in a week printing on seperate pages. Do you know why?

Thanks again for your help.

 

[dhookom]

Downloading is necessary for you to see how the report can be laid out so that your weeks don't print on separate pages. In your report, each record creates its own detail section which has a specific height. If the height of your detail section is .5" then 20 records will take up at least 10" of space. Are you trying to get around this?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sisieko]

Thank you soo much for your patience and help.

The thing is that, my report instead of display events for a certain week altogether on one page. It has only one event displaying on each page, and the next event(for the same week) displaying on the next page.

note: I grouped it so that so that it keeps data of each week together.

I don't know a better way to explain.
but i'll go download the link you gave, perhaps seeing the layout might help.

 

[dhookom]

I don't know how to explain better except that a single record will take up all the space from left to right on your report. I doubt you are doing anything that will place a "tuesday" value to the immediate right of a "monday" value.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sisieko]

thanks again.
I know that a single record will occupy from left to right.. but i've been able to bypass that by using IIF, so that each record (that satisfy that condition) displays in designated fields. I have boxes for mon, tue, wed... etc. from left to right.

But see.
I grouped by week.
All i want all records for monday to stay under that monday column, tuesday likewise. I have been able to get it that way (for each to stay in thier columns). 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, should display after the previous one (row), but it goes to the next page.
So i have many pages.. for each record.

I dunno what im doing wrong.. this is driving me nuts

 

[dhookom]

How tall are your sections? Have you added any page breaks or set any "New Page" properties in your group sections? What are your sorting and grouping fields/expressions?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sisieko]

Thanks Duane. I had to put the project aside to work on othe rthings, but im back on it now.

I have tried fixng my sections, grouping etc like you suggested.. but it still comes out looking like that. I doubt if my problem is with sections.

see this is what my report is looking like:

RoomReserv Monday Tuesday Wednesday Thursday Friday
Classroom1 4-6pm
Classroom1 7am-4pm
Classroom2 1pm-5pm
Classroom2 6am-12pm
Classroom2 4pm-6pm

_________________
Instead, Here is what im trying to achieve:

RoomReserv Monday Tuesday Wednesday Thursday Friday
Classroom1 4-6pm 7am-4pm
Classroom2 1pm-5pm 6am-12pm 4pm-6pm

Someone suggested i use a crosstab, but

It only allows for one column. Moreso, since i want timestart-timeend and program to display under each column, i made a calculated field and chose it as "value" but that doesn't work for me... error pops saying i have to declare an "aggregate function" under value.

What do you think?? Do you know how i can do this?

 

[sisieko]

*bump

 

[tstrike]

dhookom

Thank you

That link looks like it will help me with something that has been requested of me.

 

[dhookom]

sisieko,
You are probably not doing anything in your report that prints more than one record in a single section across the page. What makes you think that records from two different days but from the same Room will be printed at the same vertical area?
If you download and look at my calendar samples, you will see how this can be done.

I am currently out of town for work so my internet access is limited so please be patient.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.