Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2015
    Posts
    1

    Unanswered: Need to decipher a calculation that uses IFF in Access ...

    I'm NOT very familiar w/ Access and am having trouble figuring out exactly what this calculation is doing.
    The column is called "Months Inventory" and is calculated using the following:

    [qty_on_hand]/IIf([LastOforig_qty]=0,1,[LastOforig_qty])

    I mean, obviously it's dividing the row result from qty_on_hand by whatever the IFF is returning, but I can't figure out WHAT the IFF is returning (or specifically, HOW it's getting there) ...

    Any suggestions would be helpful. Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    suggestion
    #1 read the help file
    #2 set a watch / breakpoint on the code and examine the value of the code in the immediate window usign the debugger

    or failing that
    IIF is a boolean expression where
    iif(<logical expression>, <action if true>, <action if false>)
    so your IIF tests if LastOforig_qty is zero, if so used 1 as the divisor, if its not 0 then it uses LastOforig_qty

    it might be safer to re express this to
    Code:
    [qty_on_hand]/IIf([LastOforig_qty] <= 0, 1, [LastOforig_qty])
    and depending on where this is used you may also need to cater for NULL values
    Code:
    [qty_on_hand]/IIf([nz(LastOforig_qty,0)] <= 0, 1, [LastOforig_qty])
    the boolean expression can be a simple (as in this case a single term) or complex as you want (many tersm using and / or and so on
    you can compund IIF statements to replciate the logic of a case statement

    eg
    Code:
    myvalue = nz(myvalue,0) 'force myvalue to have a valid number, in this case 0
    iif(myvalue > 100, 'hundreds', iif(myvalue > 10, 'tens', iif (myvalue > 0, 'ones', iif (myvalue = 0, 'zero', 'negative'))))
    but compound IIF's can be a pig to set up, a pig to debug and so on
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,469
    Provided Answers: 10
    Why not turn it into a function

    in module

    Code:
    function Workitout(v1,v2,v3)
    '
    ' your logic
    '
    '
    Workitout = answer_to_Logic
    End Function
    in the query

    bla:Workitout([feildname],[nextfeildname],10)
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Posting Permissions

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