Calendar Style report missing data

[rperre]

Hello,

I'm messing around with Dhookom's calendar style report and it works like a charm, BUT.... i'm missing some data in some weeks.

This is the DateAdd line I use

WeekOf: DateAdd("d",-Weekday([RotationDate])+2,[RotationDate])

, I basically want all first days of the week (week starting on Monday). But I also only want 1 month on a sheet and on some I'm missing the 1st of the month because of this. I'm showing my sql result below.

WeekOf	WeekNum	YearNum	MonthNum
1/1/2007	1	2007	1
1/8/2007	2	2007	1
1/15/2007	3	2007	1
1/22/2007	4	2007	1
1/29/2007	5	2007	1
1/29/2007	5	2007	2
2/5/2007	6	2007	2
2/12/2007	7	2007	2
2/19/2007	8	2007	2
2/26/2007	9	2007	2
2/26/2007	9	2007	3
3/5/2007	10	2007	3
3/12/2007	11	2007	3
3/19/2007	12	2007	3
3/26/2007	13	2007	3
4/2/2007	14	2007	4
4/9/2007	15	2007	4
4/16/2007	16	2007	4
4/23/2007	17	2007	4
4/30/2007	18	2007	4
4/30/2007	18	2007	5
5/7/2007	19	2007	5

As you can see in the results it automatically makes a "double" date when the month changes for februari and march, but NOT for april and I think here is where the fault is.

Anybody suggestions???

Thanks,

Richard

 

[dhookom]

This probably because March ends on a Saturday in 2007. I'm not sure what data you think you might be missing.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rperre]

hey thanks for the quick reply,

It will not show the 1st of april week and then later also not the first of june and another one! It should show me a whole week with only the 1st of april on the sunday.

The double dates you see, do that for the other months, I just don't understand why it does not do this when the month ends on saturday.

For better understanding: in the above table you see 2 times 1/29/2007 the only difference in those lines is the month number (changes from 1 to 2) This makes this same week show up twice in the report, but since it jumps from one month to the other on one page it shows only the january days and than on the next it shows the february days from this same week. This goes the same for 2/26/2007 and 4/30/2007. But as you can see 3/26/2007 only shows once, so it doesn't show this week 2 times for the different months (did I make sense???)

And you are rigth it's only when the former month ends on a Saturday.

My report is grouped on the months and "kept together"
i also hide whatever dates are not in the month that's displayed on a page.

I'd show the reports, and can grab an image to show if you need to see what i mean,

Thanks,

Richard

 

[dhookom]

THe week of 3/26/2007 doesn't straddle two months. Why should it display in both March and April?

Maybe your issue is with the starting day of the week since the expression:
WeekOf: DateAdd("d",-Weekday([RotationDate])+2,[RotationDate])
calculates to a Monday not Sunday.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rperre]

Hey dhookom,my weeks on the reports HAVE to start on Monday, that's why I calculate for monday starts.
Is there an easy way to tell access that my weeks start on Monday in general?

Thanks, I appreciate your advice,

Richard

 

[dhookom]

How are you calculating monthnum and weeknum? Are these based on starting a week on a Monday? If not, you may need to change your expressions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rperre]

didn't think of that and might be the issue here, thanks for pointing that out!

Richard

 

[rperre]

dhookom you're the MAN, thanks a lot, problem solved

Richard