Ignore Zero and determine value

[TammyT]

I have a text box on a report that has the following "code" in it:

=IIf(([AgencyValues]>=3 And [Stnd1]>=3 And [Stnd2]>=3 And [Stnd3]>=3 And [Stnd4]>=3 And [Stnd5]>=3 And [Stnd6]>=3 And [Stnd7]>=3 And [CE1]>=3 And [CE2]>=3 And [CE3]>=3 And [CE4]>=3 And [CE5]>=3 And [CE6]>=3 And [CE7]>=3 And [CE8]>=3 And [CE9]>=3 And [CE10]>=3 And [CE11]>=3 And [CE12]>=3 And [CE13]>=3 And [CE14]>=3 And [CE15]>=3 And [CE16]>=3 And [CE17]>=3 And [CE18]>=3 And [CE19]>=3 And [CE20]>=3),"Employee consistently meets expectations with a rating of 3.00 or better on all established performance expectations.","Employee does not consistently meet expectations with a rating of 3.00 or better on all established performance expectations.")

This worked fine when it was an Access 97 Db - it ignored the zeros altogether. However, Access 2000 doesn't ignore them! So, what can I add/change for the above formula to still work, but for it also to ignore any zeros in the data table that it's pulling from (I hope this makes sense!)?

 

[glalsop]

Try wrapping all of the field names in the Nz() function. This will convert null values to 0.

=IIf((Nz([AgencyValues])>=3 And Nz([Stnd1])>=3 ...

Good luck

-Gary

 

[dhookom]

The big issue might be the un-normalized table structure. There is a sample employee evaluation mdb at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[TammyT]

Thanks - the Nz function worked! I had read about it, but didn't really understand how to get it to work (I've never had formal training in any code!). Thanks again!