Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: IIf function - Access 2013 query - 0 or null

    Hi - I have a calculated, rounded number field that I use in an IIf function, "BAL"
    It will return the correct result for less than 0 and for greater than 0, but when I tried 0 or IsNull.....it is reading those as less than 0 every time
    i checked back to my tables and the data types match (double number), but they are formatted differently.....one is general number and one is #,##0.0000

    these are the statements I tried:
    PAYTYPE: IIf([BAL]<=0,"Partial",IIf([BAL]>=0,"SEE NOTES",IIf(IsNull([BAL]),"FULL"))) and
    PAYTYPE: IIf([BAL]<=0,"Partial",IIf([BAL]>=0,"SEE NOTES",IIf([BAL]= 0,"FULL")))

    Can anyone help me to identify 0's with a similar IIf statement?

    I have played around with the math and rounding, etc....but I think my error is in the statement itself

    thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    IIF([BAL]<=0,"Partial",
    IIF([BAL]>0,"SEE NOTES",
    IIF(IsNull([BAL]),"FULL","else is not null"
    )
    )
    )

    take a step out of the trenches and what I think you are trying to do is:-
    if the balance is negative there has been a partial payment
    if its positive see the notes
    if its zero OR NULL its been paid
    ..but thats not what you are actually saying.....
    Code:
    IIf([BAL]<0,"Partial",IIf([BAL]>0,"SEE NOTES",IIf(IsNull([BAL] or BAL = 0),"FULL", "eek how trhe feck did we get here")))
    so I reckon you can shorten that to be
    Code:
    IIf([BAL]<0,"Partial",IIf([BAL]>0,"SEE NOTES","FULL"))
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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