Adding new objects Network error

[nick243]

Weekly grouping is more complicated. Do you want a week to be 7 days Sun-Sat regardless of month or year? In other words, what do you want to do about weeks that cross months and years? Also, months do not all have the same number of weeks. Can calculate the date of the first day of each week with DateAdd("d", Weekday(datefield)+1, datefield), and that ignores crossing month and year. However, that does not allow for dynamic quality in the CROSSTAB.

Calculate a week identifier with DatePart("ww", [datefield]) to use as ColumnHeader. A year seems to have 53 weeks. I tested with DatePart("ww", #12/31/2107#) with a variety of years and always get 53.

Also calculate year and month from the datefield to use as RowHeaders.

Then instead of the 12 months in the CROSSTAB, use numbers 1 through 53.

If 53 weeks does not fit your requirements will have to come up with some other algorithm to calculate weekly identifier.



Might find of interest:
https://www.accessforums.net/showthread.php?t=57472
https://www.accessforums.net/showthread.php?t=17555
https://www.accessforums.net/showthread.php?t=49247

Yes Sunday thru Saturday works for me. If the cancel by date falls with in the 7 days I want it to be shown in there with how many units from a certain department. Yes, I have seen the correct amount of weeks to be 53, I cant remember the specific page but Microsoft has a help page with that query language. I just wasn't sure how to do the headings. So I need to create 53 column headings in query design view? Thank You for your help.

[nick243]

I found the function from microsoft

Contain dates within the following week

Year([SalesDate])* 53+DatePart("ww", [SalesDate]) = Year(Date())* 53+DatePart("ww", Date()) + 1

Returns items with dates during next week. A week in Access starts on Sunday and ends on Saturday.

I think this would apply to my situation?

[June7]

If you want to pull next week's records. Change the +1 to whatever increment you want or remove it.