no****@thankyou.com wrote:
I have some data that I need to run date diffs on. The catch is that
for some of this data, there is a date value in one of the fields,
with no time. It might look like this:
ROW DT1 DT2
1 1/1/08 8:00 AM 1/1/08 10:00 AM
2 1/2/08 9:00 AM 1/2/2008
3 1/3/08 10:00 AM 1/3/08 12:00 PM
4 1/4/2008 1/4/08 1:00 PM
5 1/5/08 1:00 PM 1/5/08 2:00 PM
So row one can calculate at 2 hours. Row 2, cannot (I have another
column to take over when this is the case). Row 3 is good, 4, I need
to override. etc.
Can anyone please help me on how I stipulate this condition in an
access query?
d = date()
n = now()
? timevalue(d)
12:00:00 AM
? timevalue(n)
11:26:00 AM
? format(d,"mm/dd/yyyy hh:nn:ss")
10/22/2008 00:00:00
? format(n,"mm/dd/yyyy hh:nn:ss")
10/22/2008 11:26:00 AM
? format(d,"general date")
10/22/2008
? format(n,"general Date")
10/22/2008 11:26:00 AM
A Now() value returns date and time. Date() returns date and the time
is always 00:00:00am. I suppose you could check for the existance of a
: for those with/without a time using instr()
? instr(format(date(),"general Date"),":") = 0
True
? instr(format(now(),"general Date"),":") = 0
False
So you could create a column
Override : IIF(instr(format(date(),"general Date"),":") = 0 or _
instr(format(now(),"general Date"),":") = 0, _
"OverRide","OK:)
that displays OK or Override depending if both dates have a time.
