QUERY NEW FIELD DOESN´T ADD UP CORRECTLY

[fsweb2002]

Hi, I have a query which also creates a new field, which is the addition of several fields within the query.

The problem is that it gives me either null values as the result or a "weird" number.

For example,
value1=1275
value2=107
value3=500
value4=0
value5=6
all of the above give me 50 as the total

Another example
value1=575
value2=100
value3=500
value4=0
value5=0
all of the above give me 25 as the total.

the code I have is (apart from the usual SQL code...)

value1+value2+value3+value4+value5 AS 20Total

Your help is appreciated

 

[Steve101]

fsweb2002,
I've set up the table and query to try to duplicate your problem, but am unable to do so:

My table has each of the 5 value fields defined as Numeric, and the SQL looking like this:

SELECT Value1,Value2,Value3,Value4, Value5,
Value1+Value2+Value3+Value4+Value5 AS 20Total
FROM Table2;

This seems to operate quite satisfactorily, so ensure that your fields are defined appropriately in the table, and the SQL looks something like the above.

If you have any NULL values in any of your five value fields, you will get a NULL result for the 20Total field.
One way to resolve this is to use the nz function in the query, which converts nulls to zero prior to performing the addition; ie

nz(Value1)+nz(Value2)+nz(Value3)+nz(Value4)+nz(Value5) AS 20Total

A better way might be to prevent the nulls from being captured into the data in the first place.

I can't explain where the 50 or 25 come from, but if you ensure that your table datatypes are numeric, and that you deal with nulls, and that your SQL syntax is correct, then it ought to solve your problem.

Hope this helps. Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[fsweb2002]

Thanks for your reply Steve

It looks like it works!
I´ve doing a few trials and I am getting still some errors but it might be something different.
The 0 value seems to be woring now.

Thank you so much !