Preventing Errors on calculations where query returns no results

  • Thread starter Thread starter John Ortt
  • Start date Start date
J

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
 
Use Nz.

ie

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

HTH

Pieter

John Ortt said:
Apologies, the below refers to a control on a form.

John Ortt said:
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
Click to expand...
Click to expand...
 
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

John Ortt said:
Apologies, the below refers to a control on a form.

John Ortt said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top