Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Posts
    9

    Question Unanswered: Expression Builder (iif & Or functions)

    I need help with an expression in an Access Query. Here is my current expression:

    Weeks Supply: IIf([Total Global inventory]<1,0,[Total Global inventory]/[Weekly Avg])

    This is great but there are some fields with a "Weekly Avg" of zero so I'm getting "#error" in the field. The above expression prevents "#error" results when the "Total Global Inventory" is zero but doesn't help with the "Weekly Avg" being zero.

    Is there a way to say if "Total Global Inventory" or "Weekly Avg" is zero to put a zero in the field. If not, then perform the calculation.

    Thanks for any help. This is driving me crazy.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Weeks Supply: iif([Total Global Inventory] IS NULL OR [Weekly Avg] IS NULL, 0, ([Total Global Inventory] / [Weekly Avg]))

    Sidenote: It is bad mojo to put spaces in your table names, you would be well advised to nip that in the bud before you get to far along...

    Sidenote2: [Weekly Avg]... how is that generated? If it's an aggregate of information already in your table, that's another bad mojojito situation...
    Last edited by Teddy; 12-08-05 at 11:00.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2004
    Posts
    9

    Smile Thank you!

    Thanks so much. I just had to change the "Is Null" to "<1" and it worked.

    Also, regarding the spaces, I know but the table is linked to a spreadsheet that is updated daily from a systems data dump. This is how they have the field names in the data dump. At this point, I just left it as is so I would not have to manually go in and change the field names everyday. I need to get IT to automatically remove the spaces for me so that no extra steps need to be performed.

    Oh, the "Weekly Avg" is part of another query that calculates this field. I created this secondary query from two other querys. The "weekly avg" was just inserted into this secondary query. I'm not sure if that is a bad thing or not but it is not contained in a table because it requires being calculated.

    Again, thanks for the help!
    Last edited by cequent; 12-08-05 at 11:23.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    To combine both your solutions to cover all bases:

    Code:
     Weeks Supply: iif(NZ([Total Global Inventory], 0) <0 OR NZ([Weekly Avg], 0) <0, 0, ([Total Global Inventory] / [Weekly Avg]))
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You would still get a div/0 error. You might be able to bo nz([weekly avg], 1) in that case.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Aug 2012
    Posts
    19
    Answer = Iff(Question1,True,Iff(Question2,True,False))

Posting Permissions

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