Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    29

    Unanswered: Using IIf function on a form

    I have a form that runs from a query that shows both fields from a table and some calculations.



    Not all the records in the table have data in the fields required to do the calculations. Since they don't have the data required for the calculations they do not appear in this query.



    What I'm wanting to do is get all the records to show regardless of whether they have all the data and for those records with the data I just want it to show as 0 in those fields.



    I think I need to use the Iif function to check if there is any data for the calculation to take place and if there is to show it and if not to show as 0.



    eg IIf("Calculation1 = "" ","SHOWS THE CALCULATION", "0")



    I can't figure out how to write this so if any one can help me out it would be greatly appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Try soemthing like:-
    IIf (<mycolum>=<value>,<trueexpression>,<falseexplress ion) AS <mySQLResult>

    eg
    iif(not isnull(NoHours),NoHours*Rate,0) as billableitem should do the trick

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just as an after thought does the caluclaution need tobe done in the query - could it be usefully done in the form or report. Agree in a query is almost certainly preferable

  4. #4
    Join Date
    Sep 2005
    Posts
    29
    The Calculation Is Done In The Query

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    nz() is another option:

    nz(NoHours * Rate, 0)
    oh yeah... documentation... I have heard of that.

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

Posting Permissions

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