Group Dates In Query For Report

[Abdulkawi]

Hi,

(See screenshot)

I'm trying to group the dates by week from the begining to end of week, e.g. 01/07/2010 to 07/07/2010...etc.

How would I be able to do this?

I already tried Expr1: Format([Order_Date],"ww/mm")

But it displays the week number, not the actual week date.

Thanks!

 

[Trevor G]

You only need the ww and use datePart

DatePart("ww",[DateField])

 

[Abdulkawi]

This still displays the dates in single rows when I want them to be grouped, e.g. if 2 records are in the same date, then the values (amount) should be added together.

 

[namliam]

RevertToOneDate: Yourdate - Weekday(Yourdate, 2)

That will revert everything to the (previous) sunday, if you want monday either add + 1
Or change 2 to 3.

 

[Abdulkawi]

RevertToOneDate: Yourdate - Weekday(Yourdate, 2)

That will revert everything to the (previous) sunday, if you want monday either add + 1
Or change 2 to 3.

Same problem as above, but thanks anyway!

 

[Abdulkawi]

You only need the ww and use datePart

DatePart("ww",[DateField])

This shows that theres 53 weeks in a year, any chance I could edit the starting day of the week? It might be due to that. Thanks.

 

[Trevor G]

This shows that theres 53 weeks in a year, any chance I could edit the starting day of the week? It might be due to that. Thanks.

If you want to total the found weeks then use the Total Wizard which will add Groups in the Query, as long as you save as you go along you can then use Count from the Groups.

 

[namliam]

There are 53 weeks in a year, well some years anyways, try looking up the datepart/format function in the accewss help and find there are actually 4 settings/parameters you can use for the function:
DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

e.g. if 2 records are in the same date, then the values (amount) should be added together.

Are you doing a "group by" query? if you are, do the dates have times in them as well?

 

[Abdulkawi]

everything is working fine now.

 

[Badvoc]

How did you fix it?