Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Range on Nested IIF (a control on my report) (2003)

    Hello friends,

    I am using this nested IIF on a control on my report so that if date range btw 1/1/2006 - 3/31/2006, display 1 for the First Quarter and so on.

    <code> =IIf([targetEndDate1]>="10/1/2006","4",(IIf([targetEndDate1]>"7/1/2006" And [targetEndDate1]<="9/30/2006","3",(IIf([targetEndDate1]>"4/1/2006" And [targetEndDate1]<="6/30/2006","2",(IIf([targetEndDate1]<="3/31/2006","1")))))))</code>

    I have tried this: No luck:
    <code> =IIf([targetEndDate1]>="10/1/2006","4",(IIf([targetEndDate1] Between "7/1/2006" And "9/30/2006","3",(IIf([targetEndDate1] Between "4/1/2006" And "6/30/2006","2",(IIf([targetEndDate1]<="3/31/2006","1")))))))</code>

    Quarter 4 shows up correctly. Then every other date shows up as Quarter 1. Do you know what i could be doing wrong here? Please help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Range on Nested IIF (a control on my report) (2003)

    Try the following, it's much easier:
    - Create a text box bound to the targetEndDate field, i.e. its Control Source is the targetEndDate field.
    - Set the Format property of this text box to q (yes, that's it, simply the lower case letter Q).

    q is the formatting code for quarter of the year.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Range on Nested IIF (a control on my report) (2003)

    Like Hans said! <g>
    Or, here is an example of the function in a query grid: DatePart("q",[DateEntered]), for instance
    thx
    Pat

  4. #4
    New Lounger
    Join Date
    Feb 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Range on Nested IIF (a control on my report) (2003)

    Holy Crap! It works!!!!!!!

    wow. Thanks Hans...
    I've been fighting with that Iif statement since yesterday..lol.
    ur a life saver. I guess i should read more on that date thing! I was looking around for Format Quarter formula..couldn't find any.

    Thanks :thumbsup:

  5. #5
    New Lounger
    Join Date
    Feb 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Range on Nested IIF (a control on my report) (2003)

    Thanks Patricia. Thanks alot. You gusy are the best!
    I didn't even know there was a formula for quarter.wow. great!

Posting Permissions

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