IIF Limitation

  • Thread starter Thread starter JA
  • Start date Start date
J

JA

I am using IIF statement to specify week 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",""))))))))))))
 
JA,

How about creating another table called Weeks for instance:

StartDate EndDate Description
2004.05.01 2004.05.07 Week1
2004.05.01 2004.05.14 Week2
etc...


Then in your SQL statement you could use this:

Select t.field1, t.field2,
(SELECT w.Description
FROM Weeks w
WHERE t.StartDate Between w.StartDate And w.EndDate) AS Week
FROM table1 t
 
I suppose that this is why it is considered rude to post your question to
multiple groups: those who do not read those other groups would not know
that a solution has been provided for you already, and therefore their time
is being wasted. I'm not saying that this has happened here, but it is a
distinct possibility.

I provided this solution on another group.


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


--
Brian Kastel


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

I am using IIF statement to specify week 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",""))))))))))))
 
I like succinct code. Thanks, Dan.

--
Brian Kastel


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

Brian
Much better solution then I proposed...
Thanks,
Dan
 
Back
Top