By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

Evaluate Date/Time Field for Existence of Time

P: n/a
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?
Oct 22 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Oct 22 '08 #2

P: n/a
Thanks! That should do it!
Oct 23 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.