How to use expressions with missing values

[Maximnl]

I use a left join to join table A with table B. Table B has less matching records , so in the resulting table the field [RESULT] which comes from the table B has empty values.

If I use an expression for a new field PROCENT that involves [RESULT] I got #ERROR in all records with missing [RESULT] value. I tried various IF statements , but even IsError () function produces #ERROR.

It seems that ACCESS 2002 fails to detect missing values and gives an error for any operation. Is there any workaround for this problem?



Example
PROD_PERCENT: IIf([RESULT]>0,[RESULT]/[NETTO],0)

 

[rainman89]

have you looked into the NZ function?

 

[Rich]

Missing values, or missing records, it sounds as though you need to change the join

 

[KeithG]

The Nz function is what you need

Nz([Field],[ValueIfFieldIsNull)

 

[Maximnl]

Nz() does not help

The problem field has value #Error for the missing values, and non of Access functions iserror(), isnull() can detect it. Very strange. Any function which I apply produces #Error again.

Interesing insight: is there difference between missing records and missing values? In a join, a missing record should produce missing values. In my case something else is made by access, that it cannot even detect itself.

Some details: the right join is on two fields, but it should not cause such problems.

 

[KeithG]

Is the field already an expression? This is the only way it would say #error. The Nz function will convert a null value into a value specified by the function. I would assume you are recieving #error because you are trying to calculate a NULL value.