How to code a missing value in a Time field?

[Katie]

Any suggestions for a good "missing value" code in a Time field in Access?

I am working with a large dataset with start and end times for events of
interest. In the original data, these times were entered as 3 or 4 digit
numbers corresponding to the military time of the event of interest (e.g.
939, 1015, 1355, 2100) but not formatted as a time in Access. In this
original system, missing values were coded as either 999 or 9999 to account
for the fact that we know there is not a value for the time in that instance
(rather than leaving it blank). I am trying to convert these start and end
times into actual "Time" format so that they are more meaningful (e.g. 9:39,
10:15, etc.), however, I've run into a problem dealing with the "missing
values" as Access will not allow an invalid time (e.g. 9:99 or 99:99) and we
do not want to leave the field blank. Any ideas would be greatly
appreciated!

 

[Douglas J. Steele]

You have two choices. Leave the time Null (since you don't know what it
should be), or set it to a predefined value.

If you want to leave the time Null, you should be able to use an Update
query along the lines of:

UPDATE MyTable
SET NewTimeField = CDate(Format(Right("0" & OldTimeField, 4), "00\:00"))
WHERE TextTimeField NOT IN (999, 9999)

If you want to set it to a predefined value, try something like:

UPDATE MyTable
SET NewTimeField =
IIf(OldTimeField IN (999, 9999), #12:00:00#,
CDate(Format(Right("0" & OldTimeField, 4), "00\:00")))

Incidentally, I'd question whether you should be storing time by itself.
Access doesn't really support Time-only values: there's no Time data type,
only a Date data type. The Date data type is an 8 byte floating point number
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. That means that if all you're storing is a time, to Access it's
actually that time on 30 Dec, 1899.

 

[Ken Sheridan]

If you want to convert the values purely for presentation purposes you can
keep the existing values and format them with:

Format(YourTimeField,"0:00")

If you want to return it as an actual date/time value for doing computations
on the values, e.g. the difference between two times you can return a
date/time value with:

CDate(Format(YourTimeField,"0:00"))

Note that this would raise an error with values like 9999 or 999 which would
need to be handled.

Bear in mind that, as Doug said, there is no such thing as a Time value in
Access, nor indeed a Date value, only a DateTime value. You can see this
with your data with the following expression:

Format(Format(YourTimeField,"0:00"),"dd mmmm yyyy hh:nn:ss")

Note that its not necessary to use the CDate function when doing this.
You'll see that this will return a time on 30 December 1899, which is
day-zero in Access's implementation of the DateTime data type. If you doing
something like subtracting one time from another to get the time difference
then the date is irrelevant of course, but if you then do something like
adding the resulting time differences together to get the sum of the
durations of a number of events you'll get some strange results if the total
duration is 24 hours or more. There are ways around this, however.

Ken Sheridan
Stafford, England