Handling week numbers

[Andrew]

Hi

I am working on a database which includes a routine for importing
sales data. The data includes a "Week" field, indicated to which week
the data relates. This is a text string in the format of "2007-13"
then "2007-14" and so on.

I need to write routines to sum the sales figures for the past 4 / 6 /
8 weeks etc. However, I'm not sure how best to go about parsing this
string into a useable date, to use with dateDiff or whatever.

Can anyone suggest any techniques or websites which might help? Did a
search on Google, but didn't find anything helpful....

Thanks a lot

Andrew

 

[Douglas J. Steele]

Since the string should always be 7 characters, you can get the year using
CLng(Left([Week], 4)), and the week using CLng(Mid([Week], 6)). Even if it's
only 2007-1, rather than 2007-01 for the first week, those formulae should
work.

 

[Andrew]

Since the string should always be 7 characters, you can get the year using
CLng(Left([Week], 4)), and the week using CLng(Mid([Week], 6)). Even if it's
only 2007-1, rather than 2007-01 for the first week, those formulae should
work.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Hi

I am working on a database which includes a routine for importing
sales data. The data includes a "Week" field, indicated to which week
the data relates. This is a text string in the format of "2007-13"
then "2007-14" and so on.

I need to write routines to sum the sales figures for the past 4 / 6 /
8 weeks etc. However, I'm not sure how best to go about parsing this
string into a useable date, to use with dateDiff or whatever.

Can anyone suggest any techniques or websites which might help? Did a
search on Google, but didn't find anything helpful....

Thanks a lot

Andrew- Hide quoted text -

- Show quoted text -

Hi Doug,

Thanks for coming back to me.

Yes I realise that I can extract the various bits, but what I am less
sure of is which date function(s) to use to convert a year and a week
number into a usable date that I can compare with today's date, in the
manner of datediff or whatever. Ultimately, I need to be able to add
up a value in another field for all dates in the past 2 months, for
example.

Are you able to point me in the right direction?

Thanks again

Andrew

 

[Arvin Meyer [MVP]]

You can get the Monday of any week number with:

DateAdd("ww", [WeekNo]-1, #1/1#)

So using Doug's expression to parse out the week number, just substitute it
for [WeekNo] and get the Monday or don't subtract 1 for Sunday, the add the
number of days you want to use (probably 5 or 7) and check if the current
date is between those 2 values.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Since the string should always be 7 characters, you can get the year
using
CLng(Left([Week], 4)), and the week using CLng(Mid([Week], 6)). Even if
it's
only 2007-1, rather than 2007-01 for the first week, those formulae
should
work.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Hi

I am working on a database which includes a routine for importing
sales data. The data includes a "Week" field, indicated to which week
the data relates. This is a text string in the format of "2007-13"
then "2007-14" and so on.

I need to write routines to sum the sales figures for the past 4 / 6 /
8 weeks etc. However, I'm not sure how best to go about parsing this
string into a useable date, to use with dateDiff or whatever.

Can anyone suggest any techniques or websites which might help? Did a
search on Google, but didn't find anything helpful....

Thanks a lot

Andrew- Hide quoted text -

- Show quoted text -

Hi Doug,

Thanks for coming back to me.

Yes I realise that I can extract the various bits, but what I am less
sure of is which date function(s) to use to convert a year and a week
number into a usable date that I can compare with today's date, in the
manner of datediff or whatever. Ultimately, I need to be able to add
up a value in another field for all dates in the past 2 months, for
example.

Are you able to point me in the right direction?

Thanks again

Andrew

 

[Douglas J. Steele]

That should probably be:

DateAdd("ww", [WeekNo]-1, DateSerial([YearNo], 1, 1))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You can get the Monday of any week number with:

DateAdd("ww", [WeekNo]-1, #1/1#)

So using Doug's expression to parse out the week number, just substitute
it for [WeekNo] and get the Monday or don't subtract 1 for Sunday, the add
the number of days you want to use (probably 5 or 7) and check if the
current date is between those 2 values.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Since the string should always be 7 characters, you can get the year
using
CLng(Left([Week], 4)), and the week using CLng(Mid([Week], 6)). Even if
it's
only 2007-1, rather than 2007-01 for the first week, those formulae
should
work.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)





Hi

I am working on a database which includes a routine for importing
sales data. The data includes a "Week" field, indicated to which week
the data relates. This is a text string in the format of "2007-13"
then "2007-14" and so on.

I need to write routines to sum the sales figures for the past 4 / 6 /
8 weeks etc. However, I'm not sure how best to go about parsing this
string into a useable date, to use with dateDiff or whatever.

Can anyone suggest any techniques or websites which might help? Did a
search on Google, but didn't find anything helpful....

Thanks a lot

Andrew- Hide quoted text -

- Show quoted text -

Hi Doug,

Thanks for coming back to me.

Yes I realise that I can extract the various bits, but what I am less
sure of is which date function(s) to use to convert a year and a week
number into a usable date that I can compare with today's date, in the
manner of datediff or whatever. Ultimately, I need to be able to add
up a value in another field for all dates in the past 2 months, for
example.

Are you able to point me in the right direction?

Thanks again

Andrew

 

[Andrew]

That should probably be:

DateAdd("ww", [WeekNo]-1, DateSerial([YearNo], 1, 1))

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

You can get the Monday of any week number with:

DateAdd("ww", [WeekNo]-1, #1/1#)

So using Doug's expression to parse out the week number, just substitute
it for [WeekNo] and get the Monday or don't subtract 1 for Sunday, the add
the number of days you want to use (probably 5 or 7) and check if the
current date is between those 2 values.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

On Sep 20, 11:06 am, "Douglas J. Steele"
Since the string should always be 7 characters, you can get the year
using
CLng(Left([Week], 4)), and the week using CLng(Mid([Week], 6)). Even if
it's
only 2007-1, rather than 2007-01 for the first week, those formulae
should
work.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Hi
I am working on a database which includes a routine for importing
sales data. The data includes a "Week" field, indicated to which week
the data relates. This is a text string in the format of "2007-13"
then "2007-14" and so on.
I need to write routines to sum the sales figures for the past 4 / 6 /
8 weeks etc. However, I'm not sure how best to go about parsing this
string into a useable date, to use with dateDiff or whatever.
Can anyone suggest any techniques or websites which might help? Did a
search on Google, but didn't find anything helpful....
Thanks a lot
Andrew- Hide quoted text -
- Show quoted text -
Hi Doug,
Thanks for coming back to me.
Yes I realise that I can extract the various bits, but what I am less
sure of is which date function(s) to use to convert a year and a week
number into a usable date that I can compare with today's date, in the
manner of datediff or whatever. Ultimately, I need to be able to add
up a value in another field for all dates in the past 2 months, for
example.
Are you able to point me in the right direction?
Thanks again
Andrew- Hide quoted text -

- Show quoted text -

Great. Thanks for your help everyone - I'll give it a go.

Andrew

 

[Arvin Meyer [MVP]]

That will ALSO work <g>

Just for grins try:

DateAdd("ww", [WeekNo]-1, #1/1#)

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

That should probably be:

DateAdd("ww", [WeekNo]-1, DateSerial([YearNo], 1, 1))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You can get the Monday of any week number with:

DateAdd("ww", [WeekNo]-1, #1/1#)

So using Doug's expression to parse out the week number, just substitute
it for [WeekNo] and get the Monday or don't subtract 1 for Sunday, the
add the number of days you want to use (probably 5 or 7) and check if the
current date is between those 2 values.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

On Sep 20, 11:06 am, "Douglas J. Steele"
Since the string should always be 7 characters, you can get the year
using
CLng(Left([Week], 4)), and the week using CLng(Mid([Week], 6)). Even if
it's
only 2007-1, rather than 2007-01 for the first week, those formulae
should
work.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)





Hi

I am working on a database which includes a routine for importing
sales data. The data includes a "Week" field, indicated to which week
the data relates. This is a text string in the format of "2007-13"
then "2007-14" and so on.

I need to write routines to sum the sales figures for the past 4 / 6
/
8 weeks etc. However, I'm not sure how best to go about parsing this
string into a useable date, to use with dateDiff or whatever.

Can anyone suggest any techniques or websites which might help? Did a
search on Google, but didn't find anything helpful....

Thanks a lot

Andrew- Hide quoted text -

- Show quoted text -

Hi Doug,

Thanks for coming back to me.

Yes I realise that I can extract the various bits, but what I am less
sure of is which date function(s) to use to convert a year and a week
number into a usable date that I can compare with today's date, in the
manner of datediff or whatever. Ultimately, I need to be able to add
up a value in another field for all dates in the past 2 months, for
example.

Are you able to point me in the right direction?

Thanks again

Andrew

 

[Douglas J. Steele]

You're assuming all dates will be in the current year. My solution doesn't
make that assumption.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

That will ALSO work <g>

Just for grins try:

DateAdd("ww", [WeekNo]-1, #1/1#)

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

That should probably be:

DateAdd("ww", [WeekNo]-1, DateSerial([YearNo], 1, 1))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You can get the Monday of any week number with:

DateAdd("ww", [WeekNo]-1, #1/1#)

So using Doug's expression to parse out the week number, just substitute
it for [WeekNo] and get the Monday or don't subtract 1 for Sunday, the
add the number of days you want to use (probably 5 or 7) and check if
the current date is between those 2 values.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

On Sep 20, 11:06 am, "Douglas J. Steele"
Since the string should always be 7 characters, you can get the year
using
CLng(Left([Week], 4)), and the week using CLng(Mid([Week], 6)). Even
if it's
only 2007-1, rather than 2007-01 for the first week, those formulae
should
work.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)





Hi

I am working on a database which includes a routine for importing
sales data. The data includes a "Week" field, indicated to which
week
the data relates. This is a text string in the format of "2007-13"
then "2007-14" and so on.

I need to write routines to sum the sales figures for the past 4 / 6
/
8 weeks etc. However, I'm not sure how best to go about parsing this
string into a useable date, to use with dateDiff or whatever.

Can anyone suggest any techniques or websites which might help? Did
a
search on Google, but didn't find anything helpful....

Thanks a lot

Andrew- Hide quoted text -

- Show quoted text -

Hi Doug,

Thanks for coming back to me.

Yes I realise that I can extract the various bits, but what I am less
sure of is which date function(s) to use to convert a year and a week
number into a usable date that I can compare with today's date, in the
manner of datediff or whatever. Ultimately, I need to be able to add
up a value in another field for all dates in the past 2 months, for
example.

Are you able to point me in the right direction?

Thanks again

Andrew

 

[Arvin Meyer [MVP]]

Of course, isn't that what he asked?

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

You're assuming all dates will be in the current year. My solution doesn't
make that assumption.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

That will ALSO work <g>

Just for grins try:

DateAdd("ww", [WeekNo]-1, #1/1#)

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

That should probably be:

DateAdd("ww", [WeekNo]-1, DateSerial([YearNo], 1, 1))



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


You can get the Monday of any week number with:

DateAdd("ww", [WeekNo]-1, #1/1#)

So using Doug's expression to parse out the week number, just
substitute it for [WeekNo] and get the Monday or don't subtract 1 for
Sunday, the add the number of days you want to use (probably 5 or 7)
and check if the current date is between those 2 values.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

On Sep 20, 11:06 am, "Douglas J. Steele"
Since the string should always be 7 characters, you can get the year
using
CLng(Left([Week], 4)), and the week using CLng(Mid([Week], 6)). Even
if it's
only 2007-1, rather than 2007-01 for the first week, those formulae
should
work.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)





Hi

I am working on a database which includes a routine for importing
sales data. The data includes a "Week" field, indicated to which
week
the data relates. This is a text string in the format of "2007-13"
then "2007-14" and so on.

I need to write routines to sum the sales figures for the past 4 /
6 /
8 weeks etc. However, I'm not sure how best to go about parsing
this
string into a useable date, to use with dateDiff or whatever.

Can anyone suggest any techniques or websites which might help? Did
a
search on Google, but didn't find anything helpful....

Thanks a lot

Andrew- Hide quoted text -

- Show quoted text -

Hi Doug,

Thanks for coming back to me.

Yes I realise that I can extract the various bits, but what I am less
sure of is which date function(s) to use to convert a year and a week
number into a usable date that I can compare with today's date, in the
manner of datediff or whatever. Ultimately, I need to be able to add
up a value in another field for all dates in the past 2 months, for
example.

Are you able to point me in the right direction?

Thanks again

Andrew

 

[Douglas J. Steele]

Just looking out for January of next year, when the "past 4 / 6 / 8 weeks
etc." won't be the current year.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Of course, isn't that what he asked?

You're assuming all dates will be in the current year. My solution
doesn't make that assumption.

That will ALSO work <g>

Just for grins try:

DateAdd("ww", [WeekNo]-1, #1/1#)


That should probably be:

DateAdd("ww", [WeekNo]-1, DateSerial([YearNo], 1, 1))

You can get the Monday of any week number with:

DateAdd("ww", [WeekNo]-1, #1/1#)

So using Doug's expression to parse out the week number, just
substitute it for [WeekNo] and get the Monday or don't subtract 1 for
Sunday, the add the number of days you want to use (probably 5 or 7)
and check if the current date is between those 2 values.

On Sep 20, 11:06 am, "Douglas J. Steele"
Since the string should always be 7 characters, you can get the year
using
CLng(Left([Week], 4)), and the week using CLng(Mid([Week], 6)). Even
if it's
only 2007-1, rather than 2007-01 for the first week, those formulae
should
work.


Hi

I am working on a database which includes a routine for importing
sales data. The data includes a "Week" field, indicated to which
week
the data relates. This is a text string in the format of "2007-13"
then "2007-14" and so on.

I need to write routines to sum the sales figures for the past 4 /
6 /
8 weeks etc. However, I'm not sure how best to go about parsing
this
string into a useable date, to use with dateDiff or whatever.

Can anyone suggest any techniques or websites which might help?
Did a
search on Google, but didn't find anything helpful....

Thanks a lot

Andrew- Hide quoted text -

- Show quoted text -

Hi Doug,

Thanks for coming back to me.

Yes I realise that I can extract the various bits, but what I am less
sure of is which date function(s) to use to convert a year and a week
number into a usable date that I can compare with today's date, in
the
manner of datediff or whatever. Ultimately, I need to be able to add
up a value in another field for all dates in the past 2 months, for
example.

Are you able to point me in the right direction?

Thanks again

Andrew