Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Nested IIF Statement (97)

    Hi All,
    I have a nested iif expression that does not work for 1 condition. All the other conditions work fine.
    In my query I have sd: IIf([reqdate]=#12:00:00 AM#, IIf(IsNull([drd]),DateAdd("d",14,[wod]),[drd]),[reqdate])
    Reqdate, drd, wod are all date fields.
    If [reqdate] = 12:00 and [drd] is not null, sd = [drd] works.
    If [reqdate] does not equal 12:00 then sd = [reqdate] works.
    But if [reqdate] does not equal 12:00 and [drd] is null then sd should equal 14 days after [wod] does not work. I get a #error for the sd field.
    I tried just IIf(IsNull([drd]),DateAdd("d",14,[wod]),[drd]) in another column and it returns the correct date.
    These date fields are linked to a Foxpro DB and a Dbase DB if that would make a difference. That is where that 12:00 comes from. If [reqdate] field is blank in the Foxpro DB then it shows as 12:00:00 in Access.
    Thank you in advance.
    John

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

    Re: Nested IIF Statement (97)

    First, try replacing

    IIf(IsNull([drd]),DateAdd("d",14,[wod]),[drd])

    with

    Nz([drd],DateAdd("d",14,[wod]))

    If that doesn't help:
    Click in the sd column, then select Insert | Columns.
    Enter the following expression in the new column:

    Dummy: Nz([drd],DateAdd("d",14,[wod]))

    and change the sd column to

    sd: IIf([reqdate]=#12:00 AM#,[Dummy],[reqdate])

    BTW, does drd show real blanks?

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Nested IIF Statement (97)

    Hi Hans,
    Your first suggestion resulted in the the same error, #error.
    Your second suggestion got rid of the error but gave me a field with 2 date separators in it without a date in both the dummy column and the sd column.
    I tried sd: IIf([reqdate]=#12:00 AM#,IIf(IsNull([drd]),#1/1/2006#,[drd]),[reqdate]) just to see if it would fill in 1/1/2006 and it still gave me a #error only on the records that reqdate = 12:00 and drd was blank.
    Does drd show real blanks? There are 2 date separators. There are 2 spaces before the first /, 2 spaces between the /s, and 1 space after the last /.
    Is there anything else I can try?
    Thanks,
    John

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

    Re: Nested IIF Statement (97)

    Are you REALLY sure that drd is a date/time field?

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Nested IIF Statement (97)

    You were right Hans. WOD and DRD were not date fields even though we put a dates in those fields. Here is what I ended up with to get it to work.
    sd1: IIf([reqdate]=#12:00:00 AM#,IIf(InStr([DRD],'/ /')>0,DateValue([WOD])+14,[DRD]),[reqdate])
    If there is a better way than what I came up with, now that you have the rest of the story, please let me know.
    Thank you so much for all your help.
    John

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

    Re: Nested IIF Statement (97)

    I don't think there is a really better way. You can probably shorten the expression a bit by replacing #12:00:00 AM# with 0

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Nested IIF Statement (97)

    When I try to sort by this field I get a type mis-match. I tried to set the field's property to short date in the query but I still got the same error. Is there a way to convert this expression to a date so I can sort it?
    Thank you in advance.

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

    Re: Nested IIF Statement (97)

    Try

    sd1: IIf([reqdate]=#12:00:00 AM#,IIf(InStr([DRD],'/ /')>0,DateValue([WOD])+14,DateValue([DRD])),[reqdate])

    (Just grasping at straws here)

  9. #9
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Nested IIF Statement (97)

    Hans,
    While you were grasping at straws, you grabbed a gold ring. That fixed my problem. Thank you for your help. Have a great day.
    John

Posting Permissions

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