Time Trouble - Data Type, Query, Comparison

[WDSnews]

First I'd like hearing your opinion for a strategy for handling the time
data type. I want the ability to calculate and compare time. The database
contains text fields for start time and end time and now I think I want to
convert them to a date/time field?

Before I convert the fields from text to data/time, there is a bit of
normalizing to do and my test query is giving an error. The query seems to
work for a moment, showing the data I expect for a second or two Then the
data disappear and Access returns an error ...

"Data type mismatch in criteria expression".

I'm typing <#6:00AM# for the criteria, but you can see what Access uses for
a SQL statement.

SELECT [Classes Proposed].ID, [Classes Proposed].[Course No], [Classes
Proposed].[Course Title], Format([Start Time],"h:nn am/pm") AS StartTime,
Format([Classes Proposed].[End Time],"h:nn am/pm") AS EndTime,
CDate([Classes Proposed].[End Time]) AS convdate
FROM [Classes Proposed]
WHERE (((CDate([Classes Proposed].[End Time]))<#12/30/1899 6:0:0#));

What is the correct way to find text fields that contain a tiime before 6:00
AM?

 

[John W. Vinson]

First I'd like hearing your opinion for a strategy for handling the time
data type. I want the ability to calculate and compare time. The database
contains text fields for start time and end time and now I think I want to
convert them to a date/time field?

Before I convert the fields from text to data/time, there is a bit of
normalizing to do and my test query is giving an error. The query seems to
work for a moment, showing the data I expect for a second or two Then the
data disappear and Access returns an error ...

"Data type mismatch in criteria expression".

I'm typing <#6:00AM# for the criteria, but you can see what Access uses for
a SQL statement.

SELECT [Classes Proposed].ID, [Classes Proposed].[Course No], [Classes
Proposed].[Course Title], Format([Start Time],"h:nn am/pm") AS StartTime,
Format([Classes Proposed].[End Time],"h:nn am/pm") AS EndTime,
CDate([Classes Proposed].[End Time]) AS convdate
FROM [Classes Proposed]
WHERE (((CDate([Classes Proposed].[End Time]))<#12/30/1899 6:0:0#));

What is the correct way to find text fields that contain a tiime before 6:00
AM?

It helps to understand how Access stores date/time values. They're stored as a
Double Float number, a count of days and fractions of a day since midnight,
December 30, 1899.

As such, if you store JUST 6:00am in a datetime field, what's actually
*stored* is the number 0.25 (a quarter of a day), which indeed corresponds to
#12/30/1899 6:0:0#. The builtin Time() function will return such a value. If
you want to differentiate 6:00am on December 30, 1899 from 6:00am on May 15,
2010 from 6:00am on August 12, 1995, you should consider storing the date and
time in the same field. It depends on how you will be using the value in your
table, which I'm not sure I know. Does the Classes Proposed table contain the
date (or dates)? You may want to store the date and time together or
separately; I'm not sure which you intend.


The reason you're getting an error is that the Format() function is converting
the [StartTime] field from a Date/Time value into a Text string, and you're
comparing a Text field (a bunch of digits, letters and punctuation) to a
Date/Time field (a number). A criterion such as you wrote applied directly to
the date/time field [EndTime] should work (and no, I *won't* sign up for a
class that early... shudder!)

 

[WDSnews]

I also get a data type error from this query

SELECT [Classes Proposed].ID, DatePart("h",[Classes Proposed].[End Time]) AS
EndHour
FROM [Classes Proposed]
WHERE (((DatePart("h",[Classes Proposed].[End Time]))<6));

and this query

SELECT [Classes Proposed].ID, DatePart("h",CDate([Classes Proposed].[End
Time])) AS EndHour
FROM [Classes Proposed]
WHERE (((DatePart("h",CDate([Classes Proposed].[End Time])))<6));

....and I just don't understand. It works as soon as I remove the WHERE
clause.