IIF statement too complex?

[bsarman]

I'm trying to update records to their appropiate weeks according to the week they fall in. It works till WEEK 13, after that I get a message that says:
"Expression too complex in query expression"
Can anyone tell me where this is breaking?

Thanks.

UPDATE SR_Status_Report SET SR_Status_Report.Week =
IIf([SR_Status_Report.subdate_fintable] between #3/8/2004# And #3/14/2004#,"WEEK 1",
IIf([SR_Status_Report.subdate_fintable] between #3/15/2004# And #3/21/2004#,"WEEK 2",
IIf([SR_Status_Report.subdate_fintable] between #3/22/2004# And #3/28/2004#,"WEEK 3",
IIf([SR_Status_Report.subdate_fintable] between #3/29/2004# And #4/4/2004#,"WEEK 4",
IIf([SR_Status_Report.subdate_fintable] between #4/5/2004# And #4/11/2004#,"WEEK 5",
IIf([SR_Status_Report.subdate_fintable] between #4/12/2004# And #4/18/2004#,"WEEK 6",
IIf([SR_Status_Report.subdate_fintable] between #4/19/2004# And #4/25/2004#,"WEEK 7",
IIf([SR_Status_Report.subdate_fintable] between #4/26/2004# And #5/2/2004#,"WEEK 8",
IIf([SR_Status_Report.subdate_fintable] between #5/3/2004# And #5/9/2004#,"WEEK 9",
IIf([SR_Status_Report.subdate_fintable] between #5/10/2004# And #5/16/2004#,"WEEK 10",
IIf([SR_Status_Report.subdate_fintable] between #5/17/2004# And #5/23/2004#,"WEEK 11",
IIf([SR_Status_Report.subdate_fintable] between #5/24/2004# And #5/30/2004#,"WEEK 12",
IIf([SR_Status_Report.subdate_fintable] between #5/31/2004# And #6/6/2004#,"WEEK 13",
IIf([SR_Status_Report.subdate_fintable] between #6/7/2004# And #6/13/2004#,"WEEK 14",
IIf([SR_Status_Report.subdate_fintable] between #6/14/2004# And #6/20/2004#,"WEEK 15",
IIf([SR_Status_Report.subdate_fintable] between #6/21/2004# And #6/27/2004#,"WEEK 16",
IIf([SR_Status_Report.subdate_fintable] between #6/28/2004# And #7/4/2004#,"WEEK 17",
IIf([SR_Status_Report.subdate_fintable] between #7/5/2004# And #7/11/2004#,"WEEK 18",
IIf([SR_Status_Report.subdate_fintable] between #7/12/2004# And #7/18/2004#,"WEEK 19",
IIf([SR_Status_Report.subdate_fintable] between #7/19/2004# And #7/25/2004#,"WEEK 20", ""))))))))))))))))))));

 

[PHV]

And what about something like this ?
UPDATE SR_Status_Report SET SR_Status_Report.Week =
IIf(subdate_fintable Between #3/8/2004# And #7/25/2004#,
"WEEK " & (Format(subdate_fintable, "ww", 2)-10), "");

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bsarman]

Thank you, it worked like magic.
I appreciate your help.