Week Numbers

[Guest]

I have a crosstab query that is used as the source for a form. As a result,
the crosstab columns need to be constant. I therefore use a function within
the crosstab query to allocate the data to this week ("0"), last week ("-1")
and so on. The function is as follows:

Function weeks(week_no As String) As String
Dim week_today As Integer
week_today = Val(Format(Date, "ww"))

Select Case Val(week_no)
Case week_today
weeks = "0"
Case week_today - 1
weeks = "-1"
Case week_today - 2
weeks = "-2"
Case week_today - 3
weeks = "-3"
Case week_today - 4
weeks = "-4"
Case Else
weeks = ">4"
End Select

End Function


This has woked fine until the new year when the week numbers were reset to
zero. Can anyone help me out with a function that will work all year round.

Thanks and happy new year to you all.

 

[Duane Hookom]

I generally use a Column Heading expression of something like:

ColHead: "W" & DateDiff("ww", [YourDateField], Date())

This provides a specific set of "moving" weeks where W0 is the current week.

 

[Guest]

Thanks for your reply Duane.

Unfortunately, this suggestion doesn't work in this instance as I want to
limit the number of column headings to 6 ("0", "-1", "-2", "-3", "-4" ">4").
The reason for this is that in order to display the data in a form, using the
crosstab as its source, I must be able to keep the column names names static
as these are assigned as the data sources to individual text boxes. Your
suggestion would constantly change the column values and potentially render
the source of the text box data invalid.

Any further suggestions?

Thanks in advance

Ian

I generally use a Column Heading expression of something like:

ColHead: "W" & DateDiff("ww", [YourDateField], Date())

This provides a specific set of "moving" weeks where W0 is the current week.

--
Duane Hookom
MS Access MVP
--

I have a crosstab query that is used as the source for a form. As a result,
the crosstab columns need to be constant. I therefore use a function
within
the crosstab query to allocate the data to this week ("0"), last week
("-1")
and so on. The function is as follows:

Function weeks(week_no As String) As String
Dim week_today As Integer
week_today = Val(Format(Date, "ww"))

Select Case Val(week_no)
Case week_today
weeks = "0"
Case week_today - 1
weeks = "-1"
Case week_today - 2
weeks = "-2"
Case week_today - 3
weeks = "-3"
Case week_today - 4
weeks = "-4"
Case Else
weeks = ">4"
End Select

End Function


This has woked fine until the new year when the week numbers were reset to
zero. Can anyone help me out with a function that will work all year
round.

Thanks and happy new year to you all.

 

[Duane Hookom]

Apply my suggestion with your new requirements to get something like:

ColHead: "W" & IIf(DateDiff("ww", [YourDateField],
Date())>4,">4",DateDiff("ww", [YourDateField], Date()))

You should be able to set the column headings property to the appropriate
values.

--
Duane Hookom
MS Access MVP
--

Thanks for your reply Duane.

Unfortunately, this suggestion doesn't work in this instance as I want to
limit the number of column headings to 6 ("0", "-1", "-2", "-3", "-4"
">4").
The reason for this is that in order to display the data in a form, using
the
crosstab as its source, I must be able to keep the column names names
static
as these are assigned as the data sources to individual text boxes. Your
suggestion would constantly change the column values and potentially
render
the source of the text box data invalid.

Any further suggestions?

Thanks in advance

Ian

I generally use a Column Heading expression of something like:

ColHead: "W" & DateDiff("ww", [YourDateField], Date())

This provides a specific set of "moving" weeks where W0 is the current
week.

--
Duane Hookom
MS Access MVP
--

I have a crosstab query that is used as the source for a form. As a
result,
the crosstab columns need to be constant. I therefore use a function
within
the crosstab query to allocate the data to this week ("0"), last week
("-1")
and so on. The function is as follows:

Function weeks(week_no As String) As String
Dim week_today As Integer
week_today = Val(Format(Date, "ww"))

Select Case Val(week_no)
Case week_today
weeks = "0"
Case week_today - 1
weeks = "-1"
Case week_today - 2
weeks = "-2"
Case week_today - 3
weeks = "-3"
Case week_today - 4
weeks = "-4"
Case Else
weeks = ">4"
End Select

End Function


This has woked fine until the new year when the week numbers were reset
to
zero. Can anyone help me out with a function that will work all year
round.

Thanks and happy new year to you all.

 

[Guest]

Excellent, thanks very much Duane. Changed things around to keep the same
column headings and it works fine.

ColHead:
IIf(DateDiff("ww",Date(),[action_date])<-4,">-4",DateDiff("ww",Date(),[action_date]))

Ian

Apply my suggestion with your new requirements to get something like:

ColHead: "W" & IIf(DateDiff("ww", [YourDateField],
Date())>4,">4",DateDiff("ww", [YourDateField], Date()))

You should be able to set the column headings property to the appropriate
values.

--
Duane Hookom
MS Access MVP
--

Thanks for your reply Duane.

Unfortunately, this suggestion doesn't work in this instance as I want to
limit the number of column headings to 6 ("0", "-1", "-2", "-3", "-4"
">4").
The reason for this is that in order to display the data in a form, using
the
crosstab as its source, I must be able to keep the column names names
static
as these are assigned as the data sources to individual text boxes. Your
suggestion would constantly change the column values and potentially
render
the source of the text box data invalid.

Any further suggestions?

Thanks in advance

Ian

I generally use a Column Heading expression of something like:

ColHead: "W" & DateDiff("ww", [YourDateField], Date())

This provides a specific set of "moving" weeks where W0 is the current
week.

--
Duane Hookom
MS Access MVP
--

I have a crosstab query that is used as the source for a form. As a
result,
the crosstab columns need to be constant. I therefore use a function
within
the crosstab query to allocate the data to this week ("0"), last week
("-1")
and so on. The function is as follows:

Function weeks(week_no As String) As String
Dim week_today As Integer
week_today = Val(Format(Date, "ww"))

Select Case Val(week_no)
Case week_today
weeks = "0"
Case week_today - 1
weeks = "-1"
Case week_today - 2
weeks = "-2"
Case week_today - 3
weeks = "-3"
Case week_today - 4
weeks = "-4"
Case Else
weeks = ">4"
End Select

End Function


This has woked fine until the new year when the week numbers were reset
to
zero. Can anyone help me out with a function that will work all year
round.

Thanks and happy new year to you all.