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,418
    Provided Answers: 7
    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

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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