Calculate Week of Year

[PatK]

I have a table that I need to insert the week of the year into a field, ie, I
need to insert YYYY-WW (text) format, generated from a field I call
Create_date (date row was created in the table). My week starts on Sunday.
So, for 2008, my week 1 would be Jan 6 2008, through Jan 12 2008.
Conversely, week "53" of 2007 would be from Dec 30 2007 to Jan 5 2008. My
table has multiple years of data in it, so what I am needing needs to work
for, basically, any year, starting with 2006.

I have found that using simple functions works "some of the time" but not
all, specifically for the last week and first week of the year. If every year
started on Sunday, I could generate the field pretty simply, but alas, this
is not the case.

For example, Access considers Jan 1 2008 to Jan 8 2008 as week 1, and this
will not work for me, as my first week must begin on the first Sunday, and go
forward from there.

My plan is to go in and "fix" old/historical data, one time (adding this new
field/column), and going forward, load the correct week into the table, based
upon a calculation (probably a function) when I load the data, using VBA.

I was wondering if anyone had run into this, and already addressed it, else,
I will just have to go in and figure out the code. Thanks for any help!

PatK

 

[Douglas J. Steele]

Take a look in the Help file for DatePart or Format.

There are option parameters for firstdayofweek and firstweekofyear that you
can specify.

 

[Klatuu]

format(date,"yyyy-") & datepart("ww",date,vbSunday,vbFirstFullWeek)

 

[PatK]

Yes..I have actually been looking at that, and have been using this formula
in a query, to try to calculate this:

Create Year Week: DatePart("ww",[Create Time],vbSunday,vbFirstFullWeek)

but Access does not seem to understand the two constants (vbSunday,
vbFirstFullWeek), or (more likely), I am entering them incorrectly. Do you
see something I am doing wrong.

Note: the above is set up in query, which I would use to project a new
table to update this field for my "old" data rows. New rows, I might use a
function during my VBA load subroutine. But I am not sure if it would take
the paremeters there, either (just trying to get the logic right, before I go
there).

Thanks!

patK

 

[Klatuu]

Aha! your response to Douglas prompted my to actually think!
Queries do not understand VBA constants. You will have to use the actual
values of the constansts.
vbSunday = 1
vbFirstFullWeek = 3
Replace the constants with the values in your query.
--
Dave Hargis, Microsoft Access MVP

Yes..I have actually been looking at that, and have been using this formula
in a query, to try to calculate this:

Create Year Week: DatePart("ww",[Create Time],vbSunday,vbFirstFullWeek)

but Access does not seem to understand the two constants (vbSunday,
vbFirstFullWeek), or (more likely), I am entering them incorrectly. Do you
see something I am doing wrong.

Note: the above is set up in query, which I would use to project a new
table to update this field for my "old" data rows. New rows, I might use a
function during my VBA load subroutine. But I am not sure if it would take
the paremeters there, either (just trying to get the logic right, before I go
there).

Thanks!

patK

Take a look in the Help file for DatePart or Format.

There are option parameters for firstdayofweek and firstweekofyear that you
can specify.

 

[PatK]

BTW: Here is the function as I have it codes in a test subroutine (this is
right out of the help file):

Sub testdate()
Dim TheDate As Date ' Declare variables.
Dim Msg
TheDate = InputBox("Enter a date:")
Msg = "Week: " & DatePart("ww", TheDate, vbSunday, vbFirstFullWeek)
MsgBox Msg

End Sub

it seems to work in the subroutine, but not as a calculated field in a
query. Ideas?

patk

 

[PatK]

I guess I kinda came to that same conclusion as my little test vba routine
generates the right week. However, I would love to try to do it for the "one
time shot" in the query, to update the old records. What does the "3" mean,
tho, in

vbFirstFullWeek = 3

That is the part that is driving my crazy. Why 3? what does it mean? I
can't find anything that breaks down the possible values of that constant).

Thanks!

patk

Aha! your response to Douglas prompted my to actually think!
Queries do not understand VBA constants. You will have to use the actual
values of the constansts.
vbSunday = 1
vbFirstFullWeek = 3
Replace the constants with the values in your query.
--
Dave Hargis, Microsoft Access MVP

Yes..I have actually been looking at that, and have been using this formula
in a query, to try to calculate this:

Create Year Week: DatePart("ww",[Create Time],vbSunday,vbFirstFullWeek)

but Access does not seem to understand the two constants (vbSunday,
vbFirstFullWeek), or (more likely), I am entering them incorrectly. Do you
see something I am doing wrong.

Note: the above is set up in query, which I would use to project a new
table to update this field for my "old" data rows. New rows, I might use a
function during my VBA load subroutine. But I am not sure if it would take
the paremeters there, either (just trying to get the logic right, before I go
there).

Thanks!

patK

Take a look in the Help file for DatePart or Format.

There are option parameters for firstdayofweek and firstweekofyear that you
can specify.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a table that I need to insert the week of the year into a field, ie,
I
need to insert YYYY-WW (text) format, generated from a field I call
Create_date (date row was created in the table). My week starts on
Sunday.
So, for 2008, my week 1 would be Jan 6 2008, through Jan 12 2008.
Conversely, week "53" of 2007 would be from Dec 30 2007 to Jan 5 2008. My
table has multiple years of data in it, so what I am needing needs to work
for, basically, any year, starting with 2006.

I have found that using simple functions works "some of the time" but not
all, specifically for the last week and first week of the year. If every
year
started on Sunday, I could generate the field pretty simply, but alas,
this
is not the case.

For example, Access considers Jan 1 2008 to Jan 8 2008 as week 1, and this
will not work for me, as my first week must begin on the first Sunday, and
go
forward from there.

My plan is to go in and "fix" old/historical data, one time (adding this
new
field/column), and going forward, load the correct week into the table,
based
upon a calculation (probably a function) when I load the data, using VBA.

I was wondering if anyone had run into this, and already addressed it,
else,
I will just have to go in and figure out the code. Thanks for any help!

PatK

 

[PatK]

oops...I figured it out! Thanks, both of you!

format(date,"yyyy-") & datepart("ww",date,vbSunday,vbFirstFullWeek)