IIF to only return a value that is not a negative.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wish to only return a value if it is greater than zero.

I have used to following but I'm not sure how to make it work.

=IIf([Delay]>0,HoursAndMinutes([SiteOut]-[SiteIn]-[TipTime]),"00:00")

Any idea what I'm doing wrong?
 
I get an #error message. The HoursAndMinutes function is one I got from this
website (it works perfectly). It works out the [delay] correctly with just:

=HoursAndMinutes([SiteOut]-[SiteIn]-[TipTime])

But if the [delay] is negative I need a 00:00 value returned.

Any thoughts?

Chaim said:
What happens when you run this? HoursAndMinutes is your own function?

JohnC said:
I wish to only return a value if it is greater than zero.

I have used to following but I'm not sure how to make it work.

=IIf([Delay]>0,HoursAndMinutes([SiteOut]-[SiteIn]-[TipTime]),"00:00")

Any idea what I'm doing wrong?
Click to expand...
Click to expand...
 
I assume HoursAndMinutes is a function you have built?

Does the formula return a string?
The field the result goes into - does it accept a string?

I imagine that the formula returns a date/time, and that is what your field
is expecting. In which case, the final argument of the IIF function only
needs to be 0, not "00:00".

In any case, I suspect it is simply a matter of trying to populate the
field with a variant type that it cannot handle.

Cheers

John Webb
 
What data type is the field/variable you are putting the result in?
What is the error message you are getting?
 
Back
Top