Is there an easy way to group by week in Access ??

[jdowski]

Hi All,

In Excel there's a function "Weeknum" that you can use to help group data by week. Is there something, anything, please !!! in Access that can easily be use to perform the same function ????



Joe Dowski
Oxford, CT. USA

 

[Gastrifitis]

When you make a report using the wizard, it asks you if you want to do any grouping. If you choose a column containing dates for the grouping level, the default option is group by month. Click the Grouping Options button to change this to week.

 

[lord of the badgers]

in Access, you use the FORMAT function. FORMAT(NOW(),"WW").

 

[jdowski]

Thanks to both responders !!!

I now have what I need...
It's a little bumpy sometimes transferring my excel know how to Access but I'm learning.....

Thanks Again !!

Joe
Rainy & Warm Oxford, CT. USA

 

[lord of the badgers]

tis a pity the TEXT formula in Excel isn't as comprehensive as FORMAT in access - it's frustrating having to ask people to install the add-ins for Excel so they can use WEEKNUM

 

[jdowski]

Ok....now you've got me thinking....and in trouble again
I've created a field in my query that looks at the transaction date and then using the format function: format([transdate],"ww") gives me the week number of the trans date.

Now what I'd like to do in my query (and eventually the supporting report) is to display the first day of the particular workweek in dddd - mmmm d, yyyy format.

When I try to refer to my first expression which created the weeknumber and use another format function: format([expr1],"dddd - mmmm d, yyyy") all the results show the year as 1900 and the day being displayed are all different, not just Monday which what I would like. I realize there is a first day of the week constant I can use in the format function and I've tried it like this: format([expr1],"dddd - mmmm d, yyyy",2) but it doesn't work for me.

Help !

 

[bat17]

You also have the DatePart («interval», «date», «firstweekday», «firstweek») function in Access which has the advantage over Format() of returning Numbers rather than Text which makes sorting easier.

Peter

 

[bat17]

Looks like you might want to checkout These Functions for working with dates/times in access

HTH

Peter

 

[jdowski]

Well let me explain a little further. I have transactions that take place between Monday & Friday but sometimes the first tranaction of the week may not be until Wednesday or Thursday. Right now my report will group by weeknum via the function I wrote: format([transdate],"ww"). But I want my report header to display the the weeknum not as a number but as the first day of the workweek - Monday. What's happening now is that if I have a transaction on Monday of a particular week, then Monday is the day used for the report group heading. However, if the first transaction isn't until Wednesday, then Wednesday is being used as the heading. I would like for all the headings to be Monday irregardless of when the first transaction takes place.

 

[bat17]

If you use the weekday() function on the date you have, that will tell you which day of the week it is,and you can then subtract it to get Monday.

HTh

Peter