Re: Problem with zero result
From: Brendan Reynolds (brenreyn)
Date: 01/20/05
- Next message: [MVP] S.Clark: "Re: Data for form"
- Previous message: [MVP] S.Clark: "Re: trying to join information from a table with info from a query???"
- In reply to: cursednomore: "Re: Problem with zero result"
- Next in thread: JL: "RE: Problem with zero result"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 20 Jan 2005 23:11:53 -0000
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" <cursednomore@discussions.microsoft.com> wrote in message news:B6434157-595F-488A-9D0B-3AFE76B5C279@microsoft.com... > 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" wrote: > >> 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" <cursednomore@discussions.microsoft.com> wrote in message >> news:C82A8223-4963-4A63-9257-2349130719E0@microsoft.com... >> >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!! >> >> >>
- Next message: [MVP] S.Clark: "Re: Data for form"
- Previous message: [MVP] S.Clark: "Re: trying to join information from a table with info from a query???"
- In reply to: cursednomore: "Re: Problem with zero result"
- Next in thread: JL: "RE: Problem with zero result"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
