Short Time Format Error

[Ketan]

Hello All,

I am doing some work comparing phone calls on a bill against a telephone
usage report. Thus I have two tables (Bill and Report) and I'm running a
query comparing the date, time, call number, and length of call. However, a
lot of the comparisons are failing and I've noticed it's because of the time.
I have the fields the same for both tables (integer, text, etc.) as to avoid
errors on the query. For time, I am using the Short Time format. The
problem I have is that Access is storing some of them incorrectly. Allow me
to explain.

One of the tables is fine and shows 1:30pm as "13:30", the other table if
you're just looking at it shows "13:30", but when you click on the
field/record, it appears as "1:30:00 PM". Out of 180 records, only about 60
of them have this error and it's random in the table. Thus those 60 records
are failing my query. Any help on how to fix this? Thanks in advance!

 

[John Vinson]

Hello All,

I am doing some work comparing phone calls on a bill against a telephone
usage report. Thus I have two tables (Bill and Report) and I'm running a
query comparing the date, time, call number, and length of call. However, a
lot of the comparisons are failing and I've noticed it's because of the time.
I have the fields the same for both tables (integer, text, etc.) as to avoid
errors on the query. For time, I am using the Short Time format. The
problem I have is that Access is storing some of them incorrectly. Allow me
to explain.

One of the tables is fine and shows 1:30pm as "13:30", the other table if
you're just looking at it shows "13:30", but when you click on the
field/record, it appears as "1:30:00 PM". Out of 180 records, only about 60
of them have this error and it's random in the table. Thus those 60 records
are failing my query. Any help on how to fix this? Thanks in advance!

If you're using an Access Date/Time field for this value, the format
of the field is *absolutely irrelevant*.

Date/Time values are stored internally as a Double Float number, a
count of days and fractions of a day (times) since midnight, December
30, 1899. The format of the field is *not* part of the stored value;
it merely controls how the number is displayed.

Try setting the format of the field in the table to Long Time (don't
worry, this won't change the contents of any field). Are any of the
values off by a second or so? A field might contain the actual numeric
value 0.5625 which would *display* as 01:30pm, or as 13:30, or as
#12/30/1899 13:30:00# or any of a variety of other appearances;
another record might contain 0.562511574074074 (one second later),
which would DISPLAY as 13:30 (since forcing the hh:nn format will
truncate the remaining second) but not be equal. A Long Time would
show it as 13:30:01.

Depending on how the data was entered into the field, you might even
have two values which differ by less than a half-second; since Access
doesn't have provision for sub-second display accuracy, these values
could be different yet look the same.

John W. Vinson[MVP]

 

[Ketan]

If you're using an Access Date/Time field for this value, the format
of the field is *absolutely irrelevant*.

Date/Time values are stored internally as a Double Float number, a
count of days and fractions of a day (times) since midnight, December
30, 1899. The format of the field is *not* part of the stored value;
it merely controls how the number is displayed.

Try setting the format of the field in the table to Long Time (don't
worry, this won't change the contents of any field). Are any of the
values off by a second or so? A field might contain the actual numeric
value 0.5625 which would *display* as 01:30pm, or as 13:30, or as
#12/30/1899 13:30:00# or any of a variety of other appearances;
another record might contain 0.562511574074074 (one second later),
which would DISPLAY as 13:30 (since forcing the hh:nn format will
truncate the remaining second) but not be equal. A Long Time would
show it as 13:30:01.

Depending on how the data was entered into the field, you might even
have two values which differ by less than a half-second; since Access
doesn't have provision for sub-second display accuracy, these values
could be different yet look the same.

John W. Vinson[MVP]

The data was an import from an Excel spreadsheet. The format in the Excel
sheet was long time and all had 0 seconds, so they read "11:47:00 AM" or
"3:25:00 PM" I actually just scrolled through them again to make sure. I
assume it's some kind of import error then on Access' end, but I don't know
for sure since it's only happening to some of the records and not all. Also,
is there a way to make Access force a format on the data? Like if say I have
2 values "11:47:00 AM" and "11:47:05 AM". Is there a way I can make them
both into "11:47" as short time and have "11:47" be the new value of the
record....this way I don't have any issues of the initial data being slightly
different? Thanks again for your help.

 

[John Vinson]

The data was an import from an Excel spreadsheet. The format in the Excel
sheet was long time and all had 0 seconds, so they read "11:47:00 AM" or
"3:25:00 PM" I actually just scrolled through them again to make sure. I
assume it's some kind of import error then on Access' end, but I don't know
for sure since it's only happening to some of the records and not all.

Just doublechecking: if you look at the table in design view, is this
field Date/Time? or is it Text?

Also,
is there a way to make Access force a format on the data? Like if say I have
2 values "11:47:00 AM" and "11:47:05 AM". Is there a way I can make them
both into "11:47" as short time and have "11:47" be the new value of the
record....this way I don't have any issues of the initial data being slightly
different? Thanks again for your help.

Again, *DON'T* confuse the field *content* with the field *format*.
You can set the Format property of a field to hh:nn, but that will not
change the contents in the least!

What you may want to do, just in case, is use an Update query
squeezing the value through a call to the Format() *function* to
discard sub-minute data: update the field to

CDate(Format([fieldname], "hh:nn am/pm"))

This will take a value of 11:47:05 and convert it to a text string
"11:47 am"; CDate() will convert this back to a Date/Time and store
the truncated value back into the table.


John W. Vinson[MVP]