#Error Issue

[fenhow]

Hi, I have the following Expression that seems to work great except where there are null values for any of the fields. I have tried NZ but no luck, two fields are number fields and the other two are Date Fields. Is there a way in this expression to handle all nulls for both numbers and dates if they exist?
Thanks
Fen

Late Fee: IIf([WaiveLateFee]="Yes",0,IIf([Sum Amount Paid]=[AmountDue] And [Last Payment Made]<=[DueDate]+4,0,[AmountDue]*0.1))

 

[CJ_London]

I have tried NZ but no luck

what does that mean? nz is how you handle nulls, so how did you use it?

 

[fenhow]

Thanks, this is what I tried.

IIf([WaiveLateFee]="Yes",0,IIf(Nz([Sum Amount Paid]=[AmountDue],0) And Nz([Last Payment Made]<=[DueDate]+4,0),0,[AmountDue]*0.1))

 

[arnelgp]

IIf([WaiveLateFee]="Yes",0,IIf(Nz([Sum Amount Paid],0)=NZ([AmountDue],0) And Nz([Last Payment Made],0)<=NZ([DueDate],0)+4,0),0,[AmountDue]*0.1))

 

[CJ_London]

you missed one!>>>[AmountDue]*0.1

 

[fenhow]

Brilliant! Many thanks.
One more question.
How can I format this result to Currency?

Late Fee: IIf([WaiveLateFee]="Yes",0,IIf([Sum Amount Paid]=[AmountDue] And [Last Payment Made]<=[DueDate]+4,0,[AmountDue]*0.1))

 

[CJ_London]

you are best to do it in the control - just type 'currency' in the format property

if you do it in the query - format(myValue,"Currency") you will convert it to text which means you cannot add, subtract, multiply etc