How & Where to change the week Starting day.

[SSG QuarterMaster]

I need to my weeks to start on mondays instead of Sundays. I understsand some
of what was being said in other threads, where I am get confused is where to
put the code. I am not that familiar with the VBA aspects in MS Access. What
I need to do is group data together by a monday through Sunday week. This is
eventually going to be in a pivot table.

 

[Dale Fye]

QM,

You need to use the WeekDay function. This function accepts two parameters,
the first one is mandatory (a date), and the second (start day of week) is
optional.

For your query, you want to subtract the weekday from the date, to get a
grouping of dates within a given week. The key though, since you want your
week to start on monday is to ensure that the date returned is a monday. To
do this, try:

Monday: [DateField] - Weekday([DateField], vbTuesday)

 

[SSG QuarterMaster]

So you ar saying

Monday: [SCHED START] - Weekday([SCHED START], vbTuesday) where sched start
is my date field. The next question is where exactly would this go, in the
field column of a query or in the criteria section of a query, or would it
have to go into vba code. I will have multiple date columns that need to be
grouped like this, is there a way to do it globally for this database.

QM,

You need to use the WeekDay function. This function accepts two parameters,
the first one is mandatory (a date), and the second (start day of week) is
optional.

For your query, you want to subtract the weekday from the date, to get a
grouping of dates within a given week. The key though, since you want your
week to start on monday is to ensure that the date returned is a monday. To
do this, try:

Monday: [DateField] - Weekday([DateField], vbTuesday)

----
HTH
Dale

I need to my weeks to start on mondays instead of Sundays. I understsand some
of what was being said in other threads, where I am get confused is where to
put the code. I am not that familiar with the VBA aspects in MS Access. What
I need to do is group data together by a monday through Sunday week. This is
eventually going to be in a pivot table.

 

[Dale Fye]

I would normally put it in a query, as it is just "wrong" to store a
calculated value like this in a table (although there are a few exceptions).
If you need this for a report, you really don't even need to do the
computation as part of the query, you could do it as part of the expression
that you are using to group by.

Without a little more information about what you are trying to do with this,
I cannot give you a better answer. However, if you are going to use this
over and over in your database, I would recommend that you create a function
to avoid all the typing:

Public Function fnMonday(SomeDate as Variant) as Variant

if ISNULL(SomeDate) = True then
fnMonday = NULL
ELSE
fnMonday = SomeDate - Weekday(Somedate, vbTuesday)
endif

End Function

HTH
Dale

So you ar saying

Monday: [SCHED START] - Weekday([SCHED START], vbTuesday) where sched
start
is my date field. The next question is where exactly would this go, in the
field column of a query or in the criteria section of a query, or would it
have to go into vba code. I will have multiple date columns that need to
be
grouped like this, is there a way to do it globally for this database.

QM,

You need to use the WeekDay function. This function accepts two
parameters,
the first one is mandatory (a date), and the second (start day of week)
is
optional.

For your query, you want to subtract the weekday from the date, to get a
grouping of dates within a given week. The key though, since you want
your
week to start on monday is to ensure that the date returned is a monday.
To
do this, try:

Monday: [DateField] - Weekday([DateField], vbTuesday)

----
HTH
Dale

I need to my weeks to start on mondays instead of Sundays. I
understsand some
of what was being said in other threads, where I am get confused is
where to
put the code. I am not that familiar with the VBA aspects in MS Access.
What
I need to do is group data together by a monday through Sunday week.
This is
eventually going to be in a pivot table.