Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    47

    Unanswered: Problem with the Nz function

    Hi,

    I'm using the Nz function in a query to return a 0 in some fields when there is nothing in a field. I do so cause I want to sum these fields and I get an error because of the null values if I dont.

    My problem is, when I sum the values, it sums them as if they were text values, ... If i have 5 + 5 + 0 + 5 + 0 it returns 55050,... I need to have 15.

    Here is my Query

    Code:
    SELECT Nz([Field20],0) AS Chapt_A, tblVehicules.Field2, tblVehicules.Field3, Nz([Field22],0) AS Chapt_B1, Nz([Field23],0) AS Chapt_B2, Nz([Field25],0) AS Chapt_B3, Nz([Field26],0) AS Chapt_B4, Nz([Field29],0) AS FAQ34, Nz([Field30],0) AS SurprimeCan, ([Chapt_A]+[Chapt_B1]+[Chapt_B2]+[Chapt_B3]+[Chapt_B4]+[FAQ34]+[SurprimeCan]) AS TotalPrVehicule
    If someone can help, i'd appreciate

    Thanks
    Last edited by fuujin; 02-14-05 at 11:11.

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    if you think something has returned text wrap it in int(fieldname) as "+" also concatonates text hence the returned value above

  3. #3
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Two things.
    1. Are all the dat types Numerical ?
    2. In the code you provided I do not see the word SUM anywhere.
    Darasen

  4. #4
    Join Date
    Nov 2004
    Posts
    47
    Sorry, as english is not my first language, I have no clue what Concatonate means lol,..

  5. #5
    Join Date
    Nov 2004
    Posts
    47
    yeah, all numeric

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    concatonate is just to join all text,
    perhaps in this section
    ([Chapt_A]+[Chapt_B1]+[Chapt_B2]+[Chapt_B3]+[Chapt_B4]+[FAQ34]+[SurprimeCan])
    replace each calculation in your SQL with the actual formula i.e. Nz(field20,0) in place of your name holder

  7. #7
    Join Date
    Nov 2004
    Posts
    47
    thank you guys, and David, once again you made my day

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    nae probs, anytime

Posting Permissions

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