SALE_UNIT field displays "error" on some records

[KevinSlater]

Hi,

A query has the field PRICE_PER_100 but displays "Error" in this field when running the query on some records. I believe this is because some of the records either have the name "KILO" or "EACH" in the "STK_SALE_UNIT_MEASURE" column instead of a number. (The ones that have a number calculate fine)

Full code in query below:

PRICE_PER_100: IIf(IsNull([STK_SALE_UNIT_MEASURE]),0,[INV_PRICE]/Val([STK_SALE_UNIT_MEASURE])*100)

Any suggestions on how to fix this?, im guessing i need to change the code to display 0 if STK_SALE_UNIT_MEASURE contains either: "KILO" or "EACH" but not to sure what needs to be done.

Any help would be appreciated.

Kevin

 

[alkrm]

Try:
IIf(IsNumeric([STK_SALE_UNIT_MEASURE]),[INV_PRICE]/Val([STK_SALE_UNIT_MEASURE])*100),0)

 

[Rabbie]

Also check the NZ() function in Access Help

 

[KevinSlater]

Thanks for replies,

alkrm your solution works fine, Ill lookup the NZ function.

Thanks, Kev