changing default starting day for week numbering in access

[Guest]

Apparently when using the 'ww' expression to calculate week numbers in Access
the default starting day is a Sunday. Is there a way of changing this default
as when doing reports at our business we start on a Monday which puts things
a little bit out when trying to do comparisons.

 

[Guest]

The DatePart function has a FirstDayOfWeek argument, so you can specify
Monday using the vbMonday constant in code, or its numeric value 2 in a query:

DatePart("ww",YourDate,2)

Note that it also has a FirstWeekOfYear argument to specify which week you
want week 1 to be. By default this is the week containing 1 January; you'll
find the other options in the Help topic on the function.

Ken Sheridan
Stafford, England

 

[Joan Wild]

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])


Generally, when you provide such an expression, you can specify the starting
day. Check out the function you are using in help. For example
DatePart(interval, date[,firstdayofweek[,firstweekofyear]])

 

[Guest]

Thank you very much!

The DatePart function has a FirstDayOfWeek argument, so you can specify
Monday using the vbMonday constant in code, or its numeric value 2 in a query:

DatePart("ww",YourDate,2)

Note that it also has a FirstWeekOfYear argument to specify which week you
want week 1 to be. By default this is the week containing 1 January; you'll
find the other options in the Help topic on the function.

Ken Sheridan
Stafford, England

 

[Guest]

Joan,

I have been trolling for an answer and this post comes the closest. I am
formating a date available to come up with the interval week and year. See
below.
InvenWeek & Year: Format([Date Avail],"yy") & Format(Format([Date
Avail],"ww"),"00")

As was stated in this post earlier, it defaults to a Sunday to start the
week. How do I incorporate the DatePart to change the default to a Thursday?

Thanks for your help!

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])


Generally, when you provide such an expression, you can specify the starting
day. Check out the function you are using in help. For example
DatePart(interval, date[,firstdayofweek[,firstweekofyear]])

--
Joan Wild
Microsoft Access MVP

Apparently when using the 'ww' expression to calculate week numbers
in Access the default starting day is a Sunday. Is there a way of
changing this default as when doing reports at our business we start
on a Monday which puts things a little bit out when trying to do
comparisons.

 

[Douglas J. Steele]

Take a look in the Help file for Format: there are two optional parameters
(firstdayofweek and firstweekofyear) that you can set. Alternatively, you
could use the DatePart function (which also has those two optional
parameters) rather than Format([Date Avail], "ww")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Joan,

I have been trolling for an answer and this post comes the closest. I am
formating a date available to come up with the interval week and year.
See
below.
InvenWeek & Year: Format([Date Avail],"yy") & Format(Format([Date
Avail],"ww"),"00")

As was stated in this post earlier, it defaults to a Sunday to start the
week. How do I incorporate the DatePart to change the default to a
Thursday?

Thanks for your help!

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])


Generally, when you provide such an expression, you can specify the
starting
day. Check out the function you are using in help. For example
DatePart(interval, date[,firstdayofweek[,firstweekofyear]])

--
Joan Wild
Microsoft Access MVP

Apparently when using the 'ww' expression to calculate week numbers
in Access the default starting day is a Sunday. Is there a way of
changing this default as when doing reports at our business we start
on a Monday which puts things a little bit out when trying to do
comparisons.

 

[Guest]

Doug,

I thank you for your direction and looked at this prior to posting. I do
not understand the syntax of where I need to put the'5' in this example for a
Thursday. I have tried several places that I think might be correct but I
keep getting syntax errors. Where do should I put this option in the query I
posted earlier?

Take a look in the Help file for Format: there are two optional parameters
(firstdayofweek and firstweekofyear) that you can set. Alternatively, you
could use the DatePart function (which also has those two optional
parameters) rather than Format([Date Avail], "ww")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Joan,

I have been trolling for an answer and this post comes the closest. I am
formating a date available to come up with the interval week and year.
See
below.
InvenWeek & Year: Format([Date Avail],"yy") & Format(Format([Date
Avail],"ww"),"00")

As was stated in this post earlier, it defaults to a Sunday to start the
week. How do I incorporate the DatePart to change the default to a
Thursday?

Thanks for your help!

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])


Generally, when you provide such an expression, you can specify the
starting
day. Check out the function you are using in help. For example
DatePart(interval, date[,firstdayofweek[,firstweekofyear]])

--
Joan Wild
Microsoft Access MVP

Jono_Peek wrote:
Apparently when using the 'ww' expression to calculate week numbers
in Access the default starting day is a Sunday. Is there a way of
changing this default as when doing reports at our business we start
on a Monday which puts things a little bit out when trying to do
comparisons.

 

[Douglas J. Steele]

Format([Date Avail],"yy") & Format(Format([Date Avail],"ww", 5),"00")

or

Format([Date Avail],"yy") & Format(DatePart("ww", [DateAvail], 5),"00")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

I thank you for your direction and looked at this prior to posting. I do
not understand the syntax of where I need to put the'5' in this example
for a
Thursday. I have tried several places that I think might be correct but I
keep getting syntax errors. Where do should I put this option in the
query I
posted earlier?

Take a look in the Help file for Format: there are two optional
parameters
(firstdayofweek and firstweekofyear) that you can set. Alternatively, you
could use the DatePart function (which also has those two optional
parameters) rather than Format([Date Avail], "ww")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Joan,

I have been trolling for an answer and this post comes the closest. I
am
formating a date available to come up with the interval week and year.
See
below.
InvenWeek & Year: Format([Date Avail],"yy") & Format(Format([Date
Avail],"ww"),"00")

As was stated in this post earlier, it defaults to a Sunday to start
the
week. How do I incorporate the DatePart to change the default to a
Thursday?

Thanks for your help!

:

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])


Generally, when you provide such an expression, you can specify the
starting
day. Check out the function you are using in help. For example
DatePart(interval, date[,firstdayofweek[,firstweekofyear]])

--
Joan Wild
Microsoft Access MVP

Jono_Peek wrote:
Apparently when using the 'ww' expression to calculate week numbers
in Access the default starting day is a Sunday. Is there a way of
changing this default as when doing reports at our business we start
on a Monday which puts things a little bit out when trying to do
comparisons.

 

[Guest]

I would have never derived that - - Thanks a bunch.

Format([Date Avail],"yy") & Format(Format([Date Avail],"ww", 5),"00")

or

Format([Date Avail],"yy") & Format(DatePart("ww", [DateAvail], 5),"00")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

I thank you for your direction and looked at this prior to posting. I do
not understand the syntax of where I need to put the'5' in this example
for a
Thursday. I have tried several places that I think might be correct but I
keep getting syntax errors. Where do should I put this option in the
query I
posted earlier?

Take a look in the Help file for Format: there are two optional
parameters
(firstdayofweek and firstweekofyear) that you can set. Alternatively, you
could use the DatePart function (which also has those two optional
parameters) rather than Format([Date Avail], "ww")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Joan,

I have been trolling for an answer and this post comes the closest. I
am
formating a date available to come up with the interval week and year.
See
below.
InvenWeek & Year: Format([Date Avail],"yy") & Format(Format([Date
Avail],"ww"),"00")

As was stated in this post earlier, it defaults to a Sunday to start
the
week. How do I incorporate the DatePart to change the default to a
Thursday?

Thanks for your help!

:

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])


Generally, when you provide such an expression, you can specify the
starting
day. Check out the function you are using in help. For example
DatePart(interval, date[,firstdayofweek[,firstweekofyear]])

--
Joan Wild
Microsoft Access MVP

Jono_Peek wrote:
Apparently when using the 'ww' expression to calculate week numbers
in Access the default starting day is a Sunday. Is there a way of
changing this default as when doing reports at our business we start
on a Monday which puts things a little bit out when trying to do
comparisons.

 

[SSG QuarterMaster]

I am trying to do the same thing. I need to my weeks to start on mondays
instead of Saturdays. I understsand some of what is being said below, where I
am confused is with the "your date" section and what the value should be.
Also I am having trouble as where to put the statement. I am trying to use a
pivot table off of one of my queries. I am not that familiar with the VBA
aspects in MS Access.