Week Numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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

Duane Hookom said:
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
--

ianc said:
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.
Click to expand...
Click to expand...
 
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
--

ianc said:
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

Duane Hookom said:
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
--

ianc said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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

Duane Hookom said:
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
--

ianc said:
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

Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top