Perform calculations based on week nunber

I have created a MS Access query where one of the fields displays the
week number. The field is a date value that uses the ww format to
display the week number. However, when I try to create a calculation
such as [Week_number]*2 I get an incorrect value. For example, if the
week number is 11 and I multiply it by 2 I get an answer of 21. How do
I convert the displayed week number into an integer value so that I can
then perform a calculation on it?

re: Perform calculations based on week nunber

Peter,
Use DateAdd when adding weeks to a known date:

[mydate] = DateAdd("ww", 2, [myDate])

In addition to "ww" you can also use these:

yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second


I hope this helps,
lq



peter@cadogan.freeserve.co.uk wrote:[color=blue]
> I have created a MS Access query where one of the fields displays the
> week number. The field is a date value that uses the ww format to
> display the week number. However, when I try to create a calculation
> such as [Week_number]*2 I get an incorrect value. For example, if the
> week number is 11 and I multiply it by 2 I get an answer of 21. How[/color]
do[color=blue]
> I convert the displayed week number into an integer value so that I[/color]
can[color=blue]
> then perform a calculation on it?[/color]

re: Perform calculations based on week nunber

Hi,

It seems to work for me. When I do the following in the immediate
window datepart("ww",#02/02/2005#))*2 I get a correct answer. I tried
this with several dates and they all worked correctly. So I would look
at your field [week_number] and see if there were a reason that might
be different than an integer. Although I'm not sure why you would need
to mulitply a week number? <g>

JimA

re: Perform calculations based on week nunber

Peter,
Your Week_Number actually is a date, no matter how it is formatted.
DatePart("ww",[Week_Number]) will give you what you want.
--
Hope this helps
Arno R


<peter@cadogan.freeserve.co.uk> schreef in bericht
news:1110541792.254241.311550@o13g2000cwo.googlegr oups.com...[color=blue]
>I have created a MS Access query where one of the fields displays the
> week number. The field is a date value that uses the ww format to
> display the week number. However, when I try to create a calculation
> such as [Week_number]*2 I get an incorrect value. For example, if the
> week number is 11 and I multiply it by 2 I get an answer of 21. How do
> I convert the displayed week number into an integer value so that I can
> then perform a calculation on it?
>[/color]

re: Perform calculations based on week nunber

peter@cadogan.freeserve.co.uk wrote in message news:<1110541792.254241.311550@o13g2000cwo.googleg roups.com>...[color=blue]
> I have created a MS Access query where one of the fields displays the
> week number. The field is a date value that uses the ww format to
> display the week number. However, when I try to create a calculation
> such as [Week_number]*2 I get an incorrect value. For example, if the
> week number is 11 and I multiply it by 2 I get an answer of 21. How do
> I convert the displayed week number into an integer value so that I can
> then perform a calculation on it?[/color]

Hallo, Peter,

just have a look into this user group with "DatePart Bug in Access
2K?".

I calculated Mult2: datePart("ww";[week]*2)

and got a calculable value. But don't trust the weeknumbers around
week 52, there was a bug reported since around seven years, I guess...
Or is it really fixed?

Success

Helmut

re: Perform calculations based on week nunber

Hi all. Thanks for your comments,

What I'm trying to get is an average weekly usage: ValueA/Week_number.

Thanks

re: Perform calculations based on week nunber

sorry, I mistyped - better:

Mult2: datePart("ww";[week])*2
Best regards

Helmut

re: Perform calculations based on week nunber

Many Thanks

It works exactly as required.

Cheers

Peter