Val #Error

[jlute]

CompTotalDiff: IIf(IsNull([CompTotal]),Null,IIf([CompTotal]<>100,100-
[CompTotal]))

I believe this requires "Val" as it returns #Error if CompTotal is
Null. If there is a value it returns properly. After several attempts
at using Val I'm stuck!

CompTotal is:
CompTotal: Nz([Moisture]+Nz([Protein]+Nz([Fat]+Nz([Carbohydrates]
+Nz([Fiber]+Nz([Ash]))))))

All of the fields are number type.

Any help is greatly appreciated! Thanks!

 

[BruceM]

With Nz and a math operation you need to specify a value if null. Also, it
doesn't nest as you are trying to do. I think the default if null is an
empty string, but that won't help, so you could try something like:

CompTotal: Nz([Moisture],0) + Nz([Protein],0) + Nz([Fat],0) _
+ Nz([Carbohydrates],0) + Nz([Fiber],0) + Nz([Ash]),0)

 

[jlute]

With Nz and a math operation you need to specify a value if null.  Also, it
doesn't nest as you are trying to do.  I think the default if null is an
empty string, but that won't help, so you could try something like:

CompTotal: Nz([Moisture],0) + Nz([Protein],0) + Nz([Fat],0) _
    + Nz([Carbohydrates],0) + Nz([Fiber],0) + Nz([Ash]),0)

CompTotalDiff: IIf(IsNull([CompTotal]),Null,IIf([CompTotal]<>100,100-
[CompTotal]))

I believe this requires "Val" as it returns #Error if CompTotal is
Null. If there is a value it returns properly. After several attempts
at using Val I'm stuck!

CompTotal is:
CompTotal: Nz([Moisture]+Nz([Protein]+Nz([Fat]+Nz([Carbohydrates]
+Nz([Fiber]+Nz([Ash]))))))

All of the fields are number type.

Any help is greatly appreciated! Thanks!- Hide quoted text -

- Show quoted text -

That was indeed the problem. Sheesh! That shouldn't have been flying
as high over my head as it was!

Thanks, Bruce!

 

[BruceM]

You'll get 'em next time ;-)

With Nz and a math operation you need to specify a value if null. Also, it
doesn't nest as you are trying to do. I think the default if null is an
empty string, but that won't help, so you could try something like:

CompTotal: Nz([Moisture],0) + Nz([Protein],0) + Nz([Fat],0) _
+ Nz([Carbohydrates],0) + Nz([Fiber],0) + Nz([Ash]),0)

CompTotalDiff: IIf(IsNull([CompTotal]),Null,IIf([CompTotal]<>100,100-
[CompTotal]))

I believe this requires "Val" as it returns #Error if CompTotal is
Null. If there is a value it returns properly. After several attempts
at using Val I'm stuck!

CompTotal is:
CompTotal: Nz([Moisture]+Nz([Protein]+Nz([Fat]+Nz([Carbohydrates]
+Nz([Fiber]+Nz([Ash]))))))

All of the fields are number type.

Any help is greatly appreciated! Thanks!- Hide quoted text -

- Show quoted text -

That was indeed the problem. Sheesh! That shouldn't have been flying
as high over my head as it was!

Thanks, Bruce!