Adding fields

[Sed]

Please help!!!
The following formula is what I use to add fields, however; when I refresh my data in excel, I get an undefined "nz". the formula works in access, but I can't seem to get my excel to retrieve the data? what am I doing wrong?

ACC: (CInt(nz([Allocated Overwidth Charge Cost])+(nz([Allocated Stop Charge Cost])+(nz([Allocated Tarp Charge Cost])))))

OK, I'm made changes and it seems to work, but the column is giving me zeros ACC: IIf(IsNull([Allocated Overwidth Charge Cost])=True,0,+(IIf(IsNull([Allocated Stop Charge Cost])=True,0,+(IIf(IsNull([Allocated Tarp Charge Cost])=True,0)))))

It's not showing me the value if it's not null

 

[DCrake]

You need

ACC:Iff(IsNull([Field]=True,0,[Field])+Iff(IsNull([Field]=True,0,[Field]),etc

You have missed out the False condition

David

 

[Dennisk]

it might be easier to specify the second parameter of the nz function eg

nz([Field],0) replaces null with zero

 

[Sed]

Thanks guys,
this formula works great!! ACC: IIf(IsNull([Allocated Overwidth Charge Cost])=True,0,[Allocated Overwidth Charge Cost])+IIf(IsNull([Allocated Stop Charge Cost])=True,0,[Allocated Stop Charge Cost])+IIf(IsNull([Allocated Tarp Charge Cost])=True,0,[Allocated Tarp Charge Cost])

Dennisk, you formula works too, however; I'm trying to avoid the "nz" because I use excel to upload the data and for some reason excel does not like "nz" function. It keeps giving me "undefined 'nz' function"

HOWEVER, I do have one more question. Now that i got my "ACC" column to calculate, my next task is to create another column name: Cost_Per_Mile less "ACC" and "FSC" then divide it by distance, but every time I run it, it's asking me to enter the "ACC" Parameter?...do you know why?

 

[Sed]

the parameter I enter is "Between 1 and 3", I only wants to returns value where cost per miles is above $1 and under $3.

 

[DCrake]

It is asking for ACC because it does not know what the answer is you will need to save this query then open another query and reference this one. Then you can do further calculations.