Simple Addition Calculation Wont Work In Query 1

[JoeF]

I am tring to run a simple addition calculation in a query, but all I get is errors. I am simply trying the formula below to add 4 fields (two of which are calculated in the same query), all of which are percent formatted. I think it has to do with a type mismatch error. To attempt to correct this, I even went back to the original tables and formatted the fields as general numbers to correct the problem, but that didn't work.
newfield=[field1]+[field2]+[field3]+[field4]

Oddly enough, if I multiply each field by 1 and then add them, the formula works.
newfield=(1*[field1])+(1*[field2])+(1*[field3])+(1*[field4])

Another odd thing, if I add only two or three of the fields, sometimes the addition works, sometimes it gives me an error, and sometimes it concatenates instead of adds! This depends upon which combination of fields I choose.

I would rather have the calculation run the correct way instead of having to tell everybody to multiply by one whenever a calculation is run off these fields.

I am using Access 7.0. Would I have this problem with a more updated version of Access?

 

[THoey]

It does sound like one of the fields is being treated as a string or maybe contains a null in it. I would verify that all fields have valid values (no nulls or characters), maybe a one time query that tested each field for a numeric value and replaced it with a zero if not:

UPDATE TableName 
SET TableName.[field1] = (IIF IsNumeric([field1], [field1], 0))

I would also look into the way data is input and make sure that only numerics can be accepted.

Hope that helps...

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[jfhewitt]

I seem to recall that formatting each field as "double" may help.

 

[Rohdem]

The 'Eval' function may also help in this case. It takes a text string and evaluates it as numbers.

Mike Rohde
rohdem@marshallengines.com

 

[JoeF]

I noticed something with this query today. The fields that I have trouble calculating, are the calculate fields - spefically iff statements used to calculate numbers based on different scenarios. I have had no luck formatting these fields as numbers.

When I try to format the field with the dropdown list of pre-defined formats that show in the properties box, it gives me a list date formats and one option that says general number. However, the number format doesn't seem to fully take. I had linked a second query into the first one where I initially did the calcuation (are you following me?) and when I pulled that field in, Access didn't seem to know how to format it. When I went into the format line of the property box, the dropdown of formats was empty.

My only solution was to have the initial query with the calculations append to a table where I have the number formats set. Then I can link into that table and run further calculations off of those calculated numbers.

 

[oldgoat]

You can use the Nz function to be sure that there are no nulls.

Newfield=(Nz[field1],0)+(Nz[field2],0)+(Nz[field3],0)

Also, double check to see that all of the fields you are trying to add are indead set as numbers.