Monday as 1st day of week

[Deirdre Marie]

Afternoon~

I am using a database to compile a report which will group all dates in a weekly fashion. Unfortunately, my company recognizes Monday as the first day of the week, and not Sunday as ACCESS does. Therefore, when I sum the "weekly" totals, my figure is not correct.

I reserached here and on the MS boards and saw how one could change the global properties with an early version of ACCESS but that does not seem to be an option in Access 97.

Your assistance is appreciated.

 

[cogent1]

The Weekday Function..

is what you need. See help under this keyword in Access97. First day of the week can be what you like...

 

[Deirdre Marie]

I've now had some time to study the weekday function as suggested, but am still not able to get the results I want.

The report is based on a query which prompts the user to enter a date range and a manager code.

Once the user enters the information, the report then groups the data by weeks to give a subtotal and a grand total. However, by keeping Sunday as the first day of the week, it throws off the weekly subtotals.

I've tried using the weekday function in the Group Header control, and either I am wrinting it incorrectly (and I've written it several different ways), lor I need to place this function elsewhere.

=Weekday([Date], 2)
=Weekday([Date], Monday)
=Weekday([Date], vbMonday)

...and variations of the above 3 with different uses of [ ] and ( ) around the Monday portion.

[Date] is the control source from the query asking for the date range.

Your assistance is as always very much appreciated.
~Deirdre

 

[Rich]

Date is a reserved word in access, using it as a field name even surrounded with brackets will cause you problems,you should change it to something else