When the result of what is zero?
There are three expressions involved here ...
1) [ShdLunch]>=[Lunch Taken]
It seems unlike that you mean when the result of this expression is zero,
although it can be - it will return True or False, and False is represented
by zero.
2) [Lunch Taken]-[ShdLunch]
I think the result of this expression is probably the one you mean when you
say 'when the result is zero'?
3) The entire IIF expression, IIf([ShdLunch]>=[Lunch Taken],Null,[Lunch
Taken]-[ShdLunch])
If I'm right in thinking that number 2 above is the result to which you
refer, then JL is right, there's nothing wrong with the function. If [Lunch
Taken]-[ShdLunch] were equal to exactly zero, that would mean that
[ShdLunch]>=[Lunch Taken] would return True, therefore the entire IIF
expression would return Null. There really isn't any possible way that it
could return anything other than Null if [Lunch Taken]-[ShdLunch] were
exactly equal to zero. Therefore, [Lunch Taken]-[ShdLunch] is not exactly
zero, it only *looks* like it is exactly zero.
Are ShdLunch and Lunch Taken Date/Time fields? If so, in order to identify
the problem in the data, try creating a query like so:
SELECT Format([ShdLunch], "mm/dd/yyyyy hh:nn:ss") AS FShdLunch,
Format([Lunch Taken], "mm/dd/yyyy hh:nn:ss") AS FLunchTaken FROM
YourTableNameHere
If you are not in the US, you can use dd/mm/yyyy or whatever your usual date
format is, the point is to show the entire contents of the Date/Time field,
including the date part and the seconds part.
I'm betting that you'll find that some of the ShdLunch and Lunch Taken
values that appeared to be equal actually differ either in the date part, or
in the seconds part.
If the fields are not Date/Time fields, tell us what they are. Perhaps that
may point to other possibilities.
--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com
The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
cursednomore said:
In the other fields in the query when I use a similar formula, when the
result is zero, I get a null return (blank) as opposed to 0:00.
Brendan Reynolds said:
Think about what you just wrote for a moment ...
"When my result is exactly zero, I get 0:00 as a result"
Did you mean to write something else?
--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com
The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
cursednomore said:
I am doing a time function, and only wish to display results greater
than
zero. This is my formula: IIf([ShdLunch]>=[Lunch Taken],Null,[Lunch
Taken]-[ShdLunch])
When my result is exactly zero, I get 0:00 as a result, rather than
Null.
Can anyone explain this and/or help me correct it? I have other time
functions that do not show the 0:00, and they are formatted in the same
way.
Any help would be greatly appreciated.
Thanks!!
