Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    64

    Unanswered: IIF statement too complex?

    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", ""))))))))))))))))))));

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Believe what it is saying - that is too complex, especially for a query.

    Your specific problem may be a result of character limitation in the QBE field.

    Try using a module based function. Also consider using built in date handling functions.

    For example, it look like your "first" week is 3/8/2004 to 3/14/2004

    Now, Access "weeks" run from Sunday to Monday. Your week runs from Monday to Sunday (this will be important in a moment).

    The Format "ww" gives you the week number from the first week of the year. 3/8/2004 is actually Monday of the 11th week of the year. Since you want it to be week #1, you'll have to subtract 10. Because your week runs through Sunday, Sunday will need 11 subtracted from the date.

    Try this: =Format([SR_Status_Report.subdate_fintable],"ww")-IIf(Format([SR_Status_Report.subdate_fintable],"ddd")="Sun",11,10)

    And next time, consider using a module based function - this moster you have would be a major hit on performance if you had a large data table!

    Have fun.

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    There is an easier way ...

    There is a much easier way to solve this problem.

    What you're really doing here is obtaining a week-number based on a range of dates. So what you should do is to set up a table with:
    Date (you really only need one...)
    Week-number or name
    (and anything else you need)

    and fill it with a couple hundred years' worth of dates. "When you run out, dig me up and I'll fix it then..." Use a VB routine to populate the table.

    Now you can solve this problem, and anything vaguely resembling it in any and all of your reports, with a simple join.

    Not only is this efficient, it is also descriptive of what you're trying to accomplish, in a way that no incomprehensible chicken-scratch of an IIF() statement (or even DATEPART() calls) possibly could be. When, not "if," the application needs to be revised, new logic, new columns or what-have-you, can be added easily without breaking anything.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •