none
calculated field error RRS feed

  • Question

  • I have Quotation form that has a calculated field that is for GST and is derived from labour and material totals on the same form. The calculation for GST works fine if there is data in both labour and materials fields but doesnt  if there is nothing in the materials field as some of jobs do not require material.

    GST   =IIf(IsError(([txtLabourTotal]+[txtMaterialsTotal])*0.1),0,([txtLabourTotal]+[txtMaterialsTotal])*0.1)

    The Expected calculation is to have the GST show an amount if there is no materials to calculate for. I have tried a number of ways to overcome this problem that all had the same result. Is there a way that I can overcome this?


    • Edited by Fishy1312 Thursday, July 25, 2019 1:05 PM
    Thursday, July 25, 2019 1:01 PM

All replies

  • GST   =IIf(([txtLabourTotal]+[txtMaterialsTotal])*0.1)<=0,0,([txtLabourTotal]+[txtMaterialsTotal])*0.1)

    Assuming all variables are Number data types. Or you can also use:

    GST   =IIf(IsNull([txtLabourTotal]) Or IsNull([txtMaterialsTotal]),0,([txtLabourTotal]+[txtMaterialsTotal])*0.1)

    Thursday, July 25, 2019 1:15 PM
  • Hi,

    It's not clear for me if you get an error or nothing if one of the fields is empty. The other question is if you really want 0 when one value is missing or the value of the other field multiplied with 0.1. If both times the latter then try:

    =(nz([txtLabourTotal])+nz([txtMaterialsTotal]))*0.1

    or more explicit

    =(nz([txtLabourTotal],0)+nz([txtMaterialsTotal],0))*0.1


    Karl
    http://www.AccessDevCon.com
    http://www.donkarl.com/?aek

    Thursday, July 25, 2019 6:04 PM
  • hmmmm not sure why we're being so complicated - - indeed math will not work with a blank - it is not interpreted as a zero - - so you use the NZ function to turn nulls into zeros - just wrap the field in nz(  )

    GST = (nz([txtLabourTotal])+nz([txtMaterialsTotal]))*0.1

    Thursday, July 25, 2019 6:50 PM