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.

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

"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.

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.

Thank you very much!

"Ken Sheridan" wrote:

> 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
>
> "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.
>

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!

"Joan Wild" wrote:

> 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.
>
>
>

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
http://I.Am/DougSteele
(no e-mails, please!)


"Bunky" <(E-Mail Removed)> wrote in message
news:9576DCC4-1F83-458E-9246-(E-Mail Removed)...
> 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!
>
> "Joan Wild" wrote:
>
>> 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.
>>
>>
>>

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?


"Douglas J. Steele" wrote:

> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Bunky" <(E-Mail Removed)> wrote in message
> news:9576DCC4-1F83-458E-9246-(E-Mail Removed)...
> > 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!
> >
> > "Joan Wild" wrote:
> >
> >> 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.
> >>
> >>
> >>
>
>
>

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
http://I.Am/DougSteele
(no e-mails, please!)


"Bunky" <(E-Mail Removed)> wrote in message
news:8195D284-7CB2-4D7C-BC83-(E-Mail Removed)...
> 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?
>
>
> "Douglas J. Steele" wrote:
>
>> 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
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Bunky" <(E-Mail Removed)> wrote in message
>> news:9576DCC4-1F83-458E-9246-(E-Mail Removed)...
>> > 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!
>> >
>> > "Joan Wild" wrote:
>> >
>> >> 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.
>> >>
>> >>
>> >>
>>
>>
>>

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

"Douglas J. Steele" wrote:

> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Bunky" <(E-Mail Removed)> wrote in message
> news:8195D284-7CB2-4D7C-BC83-(E-Mail Removed)...
> > 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?
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> 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
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "Bunky" <(E-Mail Removed)> wrote in message
> >> news:9576DCC4-1F83-458E-9246-(E-Mail Removed)...
> >> > 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!
> >> >
> >> > "Joan Wild" wrote:
> >> >
> >> >> 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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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.

"Ken Sheridan" wrote:

> 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
>
> "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.
>