Results 1 to 6 of 6
  1. #1
    bdhFS is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    32

    IIf Statement...HELP!

    I am trying to query a couple of fields to base the final output. Here is what I have so far, but it is just not working...can someone please help me.

    IIf(IsNull([Num_Files] And [Failed_YN]="N","0",IIf(IsNull([Num_Files] And [Failed_YN]="Y",".",[Num_Files]))))


    So, I have a field called "Num_Files" in a table, and "Failed_YN" in another table and the two tables are linked by a key field. If the unit did not fail (N), and the Num_Files is null, then I want it to put a zero. If the unit did fail (Y), and the Num_Files is null, then I want it to put a period. Else, keep the value in the Num_Files field.


    THANKS SO MUCH!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters, you need to close off the IsNull function:

    IIf(IsNull([Num_Files]) And [Failed_YN]="N",...

    Is Failed_YN a text field? That's how you're treating it. If it's a Yes/No field:

    And [Failed_YN]=False

    or

    And [Failed_YN]=0
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    bdhFS is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    32

    Got it to work

    Thank you so much for your reply Paul. That is exactly what I was missing...")" and it is working.

    I was curious...is there a difference or preferance in doing it this way instead...instread of using the IsNull Function?

    IIf([Num_Files] is null And [Failed_YN]="N"

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help. I'm not sure there would be any performance difference either way. I'd use whichever one you're more comfortable with.

    Welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    bdhFS is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    32

    Thankful!

    I am so thankful for people like you...who help people like me! I am greatful for resources like this that help me get my work done! I am sure I will be back over and over again!

  6. #6
    CGM3 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Atlanta, GA
    Posts
    49
    Just a suggestion, I'd go with:

    IIf(IsNull([Num_Files]), IIf([Failed_YN]="Y", ".", "0"), [Num_Files])

    In the original format, if [Num_Files] was null and [Failed_YN] happened to be anything other than "Y" or "N", the formula would have bombed when it tried to apply the (null) [Num_Files]. This way, if [Num_Files] is null, you return "." if [Failed_YN] is "Y" and "0" otherwise.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. If statement for image
    By turbo910 in forum Forms
    Replies: 1
    Last Post: 11-30-2009, 04:58 AM
  2. If statement Issue????
    By graviz in forum Programming
    Replies: 1
    Last Post: 09-25-2009, 10:09 AM
  3. IIF statement
    By james1982 in forum Access
    Replies: 1
    Last Post: 07-20-2009, 09:38 AM
  4. how to use IF then statement
    By ronnie4 in forum Access
    Replies: 1
    Last Post: 03-17-2009, 11:32 PM
  5. multiple iif statement NEED HELP PLZ
    By scott munkirs in forum Reports
    Replies: 1
    Last Post: 09-27-2006, 05:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums