IIF Limitation

[JA]

I am using IIF statement to specify weeks as shown below.
Unfortunately it does not allow me to go beyond 13 weeks.
How can I overcome this limitation?

TrainingWeek: IIf([StartDate] Between #01/May/04# And
#07/May/04#,"Week 1",IIf([StartDate] Between #08/May/04#
And #14/May/04#,"Week 2",IIf([StartDate] Between
#15/May/04# And #21/May/04#,"Week 3",IIf([StartDate]
Between #21/May/04# And #28/May/04#,"Week 4",IIf
([StartDate] Between #29/May/04# And #04/Jun/04#,"Week
5",IIf([StartDate] Between #05/Jun/04# And
#11/Jun/04#,"Week 6",IIf([StartDate] Between #12/Jun/04#
And #18/Jun/04#,"Week 7",IIf([StartDate] Between
#19/Jun/04# And #25/Jun/04#,"Week 8",IIf([StartDate]
Between #26/Jun/04# And #02/Jul/04#,"Week 9",IIf
([StartDate] Between #03/Jul/04# And #09/Jul/04#,"Week
10",IIf([StartDate] Between #10/Jul/04# And
#16/Jul/04#,"Week 11",IIf([StartDate] Between #17/Jul/04#
And #23/Jul/04#,"Week 12",""))))))))))))

 

[MyndPhlyp]

I am using IIF statement to specify weeks as shown below.
Unfortunately it does not allow me to go beyond 13 weeks.
How can I overcome this limitation?

TrainingWeek: IIf([StartDate] Between #01/May/04# And
#07/May/04#,"Week 1",IIf([StartDate] Between #08/May/04#
And #14/May/04#,"Week 2",IIf([StartDate] Between
#15/May/04# And #21/May/04#,"Week 3",IIf([StartDate]
Between #21/May/04# And #28/May/04#,"Week 4",IIf
([StartDate] Between #29/May/04# And #04/Jun/04#,"Week
5",IIf([StartDate] Between #05/Jun/04# And
#11/Jun/04#,"Week 6",IIf([StartDate] Between #12/Jun/04#
And #18/Jun/04#,"Week 7",IIf([StartDate] Between
#19/Jun/04# And #25/Jun/04#,"Week 8",IIf([StartDate]
Between #26/Jun/04# And #02/Jul/04#,"Week 9",IIf
([StartDate] Between #03/Jul/04# And #09/Jul/04#,"Week
10",IIf([StartDate] Between #10/Jul/04# And
#16/Jul/04#,"Week 11",IIf([StartDate] Between #17/Jul/04#
And #23/Jul/04#,"Week 12",""))))))))))))

Rather than using a highly redundant IIF statement, write up a Function that
calculates the week number based on a known starting point and returns a
string literal for the week. The calculation is relatively simple:

NbrOfDays = MyDate - StartingPoint + 1
NbrOfWeeks = Round(NbrOfDays / 7, 0)
If (NbrOfDays Mod 7) > 0 Then
NbrOfWeeks = NbrOfWeeks + 1
End If

It should also be faster than the nested IIF statement as the dates get
later and later.

 

[Brian Kastel]

Try this:

TrainingWeek: Int(DateDiff("d", #01/May/04#, StartDate) / 7) + 1

--
Brian Kastel


--Original Message----------------

I am using IIF statement to specify weeks as shown below.
Unfortunately it does not allow me to go beyond 13 weeks.
How can I overcome this limitation?

TrainingWeek: IIf([StartDate] Between #01/May/04# And
#07/May/04#,"Week 1",IIf([StartDate] Between #08/May/04#
And #14/May/04#,"Week 2",IIf([StartDate] Between
#15/May/04# And #21/May/04#,"Week 3",IIf([StartDate]
Between #21/May/04# And #28/May/04#,"Week 4",IIf
([StartDate] Between #29/May/04# And #04/Jun/04#,"Week
5",IIf([StartDate] Between #05/Jun/04# And
#11/Jun/04#,"Week 6",IIf([StartDate] Between #12/Jun/04#
And #18/Jun/04#,"Week 7",IIf([StartDate] Between
#19/Jun/04# And #25/Jun/04#,"Week 8",IIf([StartDate]
Between #26/Jun/04# And #02/Jul/04#,"Week 9",IIf
([StartDate] Between #03/Jul/04# And #09/Jul/04#,"Week
10",IIf([StartDate] Between #10/Jul/04# And
#16/Jul/04#,"Week 11",IIf([StartDate] Between #17/Jul/04#
And #23/Jul/04#,"Week 12",""))))))))))))