Preventing Errors on calculations where query returns no results

[John Ortt]

Hi everyone

I have a query which depending on the users selection may or may-not return
any results.

The expression below works fine when results are returned but when there are
no records it returns '#Error':

=IIf([expirydays]<1,"expired " & Abs([expirydays]) & " days ago")

Is there a way to modify it to gracefully fall over (ie be blank) when there
are no records?

I have tried 'IIf([expirydays] is null,null' and
'IIf(len([expirydays])<1,null' but neither worked.

Thanks in advance,

John

 

[John Ortt]

Apologies, the below refers to a control on a form.

 

[Pieter Wijnen]

Use Nz.

ie

=IIf(Nz([expirydays],0)<1,"expired " & Abs(Nz([expirydays],0)) & " days
ago")

HTH

Pieter

Apologies, the below refers to a control on a form.

Hi everyone

I have a query which depending on the users selection may or may-not
return any results.

The expression below works fine when results are returned but when there
are no records it returns '#Error':

=IIf([expirydays]<1,"expired " & Abs([expirydays]) & " days ago")

Is there a way to modify it to gracefully fall over (ie be blank) when
there are no records?

I have tried 'IIf([expirydays] is null,null' and
'IIf(len([expirydays])<1,null' but neither worked.

Thanks in advance,

John

 

[John Ortt]

Thanks for the help Pieter,

It doesn't seem to have worked at the moment but I'll have a play around
incase I have pasted it incorrectly.



"Pieter Wijnen"

Use Nz.

ie

=IIf(Nz([expirydays],0)<1,"expired " & Abs(Nz([expirydays],0)) & " days
ago")

HTH

Pieter

Apologies, the below refers to a control on a form.

Hi everyone

I have a query which depending on the users selection may or may-not
return any results.

The expression below works fine when results are returned but when there
are no records it returns '#Error':

=IIf([expirydays]<1,"expired " & Abs([expirydays]) & " days ago")

Is there a way to modify it to gracefully fall over (ie be blank) when
there are no records?

I have tried 'IIf([expirydays] is null,null' and
'IIf(len([expirydays])<1,null' but neither worked.

Thanks in advance,

John