Iif Iserror with multiplication

[jrhessey]

I'm trying to create an expression field in my query that multiplies 2 fields that are in the query. Here is the code I'm using...

NTA#: IIf(IsError([HeaderProductionQuantity]*[NTA]),0,[HeaderProductionQuantity]*[NTA])

The quantity field has the amount of pieces that we produced in a given day. The NTA field is a number 1-4 and blank that show the amount of pieces that require the NTA job.

As long as there is a number in the nta field, I get a good calculation. If the nta field is blank, I get #error in the query. What is wrong with the above formula that it isn't replacing the #error with 0?

If I put the 0 at the end of the formula, it replaces the good calcs with 0,so the formula is correct, correct?

Any is greatly appreciated!! Thanks!

 

[pbaldy]

Try this:

NTANum: [HeaderProductionQuantity]*nz([NTA],0)

which should work if the blanks are Nulls.

 

[jrhessey]

Hey that works, I tried replacing the 0 with "" to create a blank cell and got my beloved #error back. How can I change your formula from 0 to blank? Thanks for your help!!

 

[pbaldy]

This may work:

NTANum: nz([HeaderProductionQuantity]*[NTA],"")

 

[jrhessey]

awesome! Thanks for your help!

 

[jrhessey]

Ok, I ran into a little problem trying to do a total length. I had to convert the length field to number, I was getting the #error, before I added the val() part tot the formula. My little problem is no that formula returns a 0 not a blank like the other formula. What can I change to make it return a blank instead of zero. Thanks!

TotalLength: nz([HeaderProductionQuantity]*Val([Length]),"")