Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: add 3+ fields with 1 null value

    I am trying to add
    [Overtime]+[Salary]+[Commission] AS [Gross Pay]
    if one of the fields is empty, then gross pay is empty

    how do i get billgates to add empty field+field+field=##

    like 0+1+1=2, but the 0 = empty field

    i have always thought that 1+1=2, and 0+1=1? instead of 0+1=null

    below is sql statement

    thanks in advance



    SELECT tbl_Company.COname, tbl_Employees.Department, [tbl_2004-PR].PP, tbl_Employees.FirstName, tbl_Employees.MiddleName, tbl_Employees.LastName, [tbl_2004-PR].HrsOverTime, [tbl_2004-PR].Overtime, [tbl_2004-PR].Salary, [tbl_2004-PR].Commission, [Overtime]+[Salary]+[Commission] AS [Gross Pay]
    FROM tbl_Company INNER JOIN ([tbl_2004-PR] INNER JOIN tbl_Employees ON [tbl_2004-PR].Enumber = tbl_Employees.Enumber) ON tbl_Company.IDcompany = tbl_Employees.IDcompany
    ORDER BY tbl_Employees.Department, tbl_Employees.LastName;

  2. #2
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    Use the nz() function in your statement on any field that may contain a null value. It will convert null to zero for the calculation.

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    Originally posted by bmacr
    Use the nz() function in your statement on any field that may contain a null value. It will convert null to zero for the calculation.
    thanks, but where do you put the nz()??

  4. #4
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    hi
    i agree with the nz() function but it may be wise to always default a 0 (zero) in the form/table that way youll highlight a missed input for part of wage operation


    regards
    gareth

  5. #5
    Join Date
    Feb 2004
    Location
    Canada
    Posts
    133
    Originally posted by hdcfh
    thanks, but where do you put the nz()??
    You can put it anywhere you'd expect a null value to cause a problem.

    nz([Overtime])+nz([Salary])+nz([Commission]) AS [Gross Pay]

  6. #6
    Join Date
    Mar 2004
    Posts
    3
    thanks, i just figured it out

    saved me a lot of time

    thanks

Posting Permissions

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