Date/Time Fields criteria

[Simon]

Hi,

I have a table "Training_Session", two attributes in the table, are "date"
and "finish_time". The date represents the date the training session is
held, the finish time is the time that the training_session is completed.
They are both Date/Time Fields, the key thing to note is that "date" hasn't
had its time set, only the actual date itself e.g 2/5/2005 is set. Also
finish_time only has its time values set e.g 02:00:00 PM.

What i'm trying to do is write a query that displays all Training_Sessions
that are before the system date. So I need to some how write something that
checks that the date is less than or equal to the system date, and if its
equal well then it needs to check that the "finish_time" is less than the
system time.

Here is an example of my "Training_Session" table. assume system date =
24/9/2005 11:48:00 AM
Session_id Date finish_time
S0001 24/9/2005 11:49:00 AM
S0002 24/9/2005 11:47:00 AM
S0003 4/1/2005 02:00:00 PM

So the query should return S0002 and S0003 because the "date" and
"finish_time" is less than the system date.


Your help is urgently required and much appreciated.
Simon

 

[Douglas J. Steele]

Is there a reason for not combining date and finish_time in a single field?
It really is the preferred approach. (If you need only the date portion, or
only the time portion for some reason, you can use the DateValue and
TimeValue functions)

If you can't combine them, you can simply add the two fields together.

BTW, Date isn't a good choice for a field name. It's a reserved word, and
using a reserved word for your own purposes is seldom a good idea.

 

[Simon]

Hi douglas,

I need to keep the fields separate, how can I add the two fields together?

Thanks.
Simon

 

[Simon]

I managed to concatenate the two fields into one using this expression:
Expr1: DateValue([Training_Session.date]) & " " &
TimeValue([Training_Session.finish_time])

It works ok, however when it comes to comparing the times when the dates are
equal, it makes a mistake for example:
it thinks that 24 Sep 2005 12:30:00PM is greater than 24 Sep 2005 1:00:00PM.
It thinks this because 12 is greater than 1.

How can i rectify this. Your help is much appreciated.

Thanks.
Simon

 

[Douglas J. Steele]

WHY do you need to keep them separate? I cannot think of a single possible
reason!

As I said, if you need to be able to get to just the date portion, or just
the time portion, you can create a query based on the table that includes
two computed fields, one using the DateValue function on your combined
field, and the other using the TimeValue function. You can use that query
wherever you would otherwise have used the table.

As to how you add the two fields together, it's literally addition: [Date] +
[finish_time]

Date/Time values are stored as 8 byte floating points numbers where the
integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day. If you have a time-only value, it's stored with a date portion of 0,
or, in other words, as a time on 30 Dec, 1899. You can see this by applying
an explicit format to the value.

 

[Jeff Boyce]

Simon

If you are only throwing the pieces together, Access might not know that you
intend the combination to be a date/time value. ?Perhaps if you used the
CDate() function to explicitly type the combination?

--
Regards

Jeff Boyce
<MS Office/Access MVP>

I managed to concatenate the two fields into one using this expression:
Expr1: DateValue([Training_Session.date]) & " " &
TimeValue([Training_Session.finish_time])

It works ok, however when it comes to comparing the times when the dates are
equal, it makes a mistake for example:
it thinks that 24 Sep 2005 12:30:00PM is greater than 24 Sep 2005 1:00:00PM.
It thinks this because 12 is greater than 1.

How can i rectify this. Your help is much appreciated.

Thanks.
Simon

Hi douglas,

I need to keep the fields separate, how can I add the two fields together?

Thanks.
Simon