Changing Week Start Day

[Meinthecorner]

Access 2007, Crosstab query .

Evening - We've just started opening our business on a Sunday and this little issue has just manifested itself!

We record volumes of calls previously just Monday to Saturday and the call volumes are grouped by week number. Which worked fine, now we open Sunday the Sunday values are pushed to the next week number. The customer wants all data for the week grouped Monday to Sunday, otherwise I'd just let the pushed week roll into the next week.

So for this week 16th to 22nd May, my week number would normally be 21. But the 22nd data is added to week 22

The current query field is:

Format([CallDate],"ww")

This works. I've done some digging and found this possible suggestion

 Val(Format([WorkingDate], "ww", vbMonday))

So my query field looked like this

Val(Format([CallDate],"ww", vbMonday))

But when I leave the field it adds [] round the vbMonday, and the query fails.

So any pointers so that my week number group goes from Monday to Sunday?

 

[stopher]

Try:

Val(Format([CallDate],"ww", 2))

hth
Chris

 

[gemma-the-husky]

i think its just that you can't use the vbconstant (vbmonday) in a QUERY - although you can use it in code OK.

 

[Meinthecorner]

Thanks Peeps, changing to a 2 sorted the problem