calculating the # of weeks in a period with variable start and e.

[Guest]

Have a form with fields "Date" "Survey Period" and "Week." There are four
survey periods during a 52 week year that begins on 11/3/2004. Each survey
period has 13 weeks, so the first week of the second survey period would be
2/2/05, and so on. I want the field labeled "week" to indicate the Value of
a week, 1 to 13, upon entering a date into the "Date" field. I've already
been able to calculate the Value of the Survey Period by using an "If"
Function in a module with the Datediff , which I learned courtesy of one my
one free call to MS help. I have tried using the IF/Else function for my
question, but it returns a default date in the "Date" field, which causes as
values to change to reflect the most recently entered date/week. I tried
putting into Expression Bldr as (If Between and,x) & (If Between and, x) but
ran out room in the control source property box after about 30+ entries.



Sorry for the long message! Thank you



Gary

 

[John Vinson]

Have a form with fields "Date" "Survey Period" and "Week."

I'd strongly suggest changing the name of the field Date: that's a
reserved word for the Date() function which returns today's date from
the computer clock.

There are four
survey periods during a 52 week year that begins on 11/3/2004. Each survey
period has 13 weeks, so the first week of the second survey period would be
2/2/05, and so on. I want the field labeled "week" to indicate the Value of
a week, 1 to 13, upon entering a date into the "Date" field.

The field *should not exist* in your table, since it can be derived
from the date. Calculate it on the fly instead.

I'd suggest using

Week: DateDiff("ww", DateSerial(Year(Date()), 3*(DatePart("q",
[renameddatefield]) - 1) + 1), [renameddatefield])

in a vacant Field cell in a Query.

Air code, untested...

John W. Vinson[MVP]

 

[Guest]

Thank you, John- you're respone is appreciate. I'll give it a try as you
suggest.

Have a form with fields "Date" "Survey Period" and "Week."

I'd strongly suggest changing the name of the field Date: that's a
reserved word for the Date() function which returns today's date from
the computer clock.

There are four
survey periods during a 52 week year that begins on 11/3/2004. Each survey
period has 13 weeks, so the first week of the second survey period would be
2/2/05, and so on. I want the field labeled "week" to indicate the Value of
a week, 1 to 13, upon entering a date into the "Date" field.

The field *should not exist* in your table, since it can be derived
from the date. Calculate it on the fly instead.

I'd suggest using

Week: DateDiff("ww", DateSerial(Year(Date()), 3*(DatePart("q",
[renameddatefield]) - 1) + 1), [renameddatefield])

in a vacant Field cell in a Query.

Air code, untested...

John W. Vinson[MVP]

 

[Guest]

Function Get_Survey_Week(dteSurveyDate As Date) As Integer
Dim dteBaseDate As Date
Dim lngTotWks As Long

dteBaseDate = #10/27/2004# 'Set Beginning date for periods back one week
'so calculation returns correct value
'Get the gross difference in weeks
lngTotWks = DateDiff("ww", dteBaseDate, dteSurveyDate, vbWednesday)
'If more than 13, then calculate the number of periods and find current
period start date
If lngTotWks > 13 Then
dteBaseDate = DateAdd("ww", Int(lngTotWks / 13) * 13, dteBaseDate)
lngTotWks = DateDiff("ww", dteBaseDate, dteSurveyDate, vbWednesday)
End If
Get_Survey_Week = lngTotWks
End Function

 

[Guest]

Klatuu

Thank you so much for your reply and information you provided with the
function! I will give it a try and let you know what happened.

 

[Guest]

Hi John,

When I entered the formula in a query, I got a response stating the argument
had the wrong number of arguments. Any thoughts on what I might have done
wrong? I am kind of new to Access, so fogive my clumsiness.

Thank you,

Gary

Have a form with fields "Date" "Survey Period" and "Week."

I'd strongly suggest changing the name of the field Date: that's a
reserved word for the Date() function which returns today's date from
the computer clock.

There are four
survey periods during a 52 week year that begins on 11/3/2004. Each survey
period has 13 weeks, so the first week of the second survey period would be
2/2/05, and so on. I want the field labeled "week" to indicate the Value of
a week, 1 to 13, upon entering a date into the "Date" field.

The field *should not exist* in your table, since it can be derived
from the date. Calculate it on the fly instead.

I'd suggest using

Week: DateDiff("ww", DateSerial(Year(Date()), 3*(DatePart("q",
[renameddatefield]) - 1) + 1), [renameddatefield])

in a vacant Field cell in a Query.

Air code, untested...

John W. Vinson[MVP]

 

[Guest]

Hi Klatuu

I tried this function and it keeps returning a value of "0" regardless of
the date entered in the SurveyDate field. I changed the name of the Date
field to SurveyDate field in the table and the form, and I let the default
value be "Date()". I also tried it with no default value on the form or
table in that column. Can you think of anything I may be doing wrong?

Thank you

Gary

 

[Guest]

Klatue

Please disregard my previous message concerning the "o" return value. I
found an error in my input (failed to put int "Get_ Survey_...right before
the last line, "End Function." Now, the function works.

Thanks again for the help- much appreciated!


Gary