24 hour time format conversion

[JAV]

Hello,

I have a database that I have been trying to convert into a correct
and recognizable time format. The old db kept time in numeric form
01...2400. The problem is when I try to import this into the new
database using CDate() it gives and error for each 2400 hour. I
understand that Access (2003) expresses 2400 hours as 0:00 but cannot
seem to get the right conversion for the append query. Here is what I
have for the query expression:

TimeValue: Date(IIf([Stored_Sampler_Data]![Time]<60,
(Format([Stored_Sampler_Data]![Time],"00@")),
(Format([Stored_Sampler_Data]![Time],"@@@"))))

I have attempted to use the switch function for choosing the case and
that was not successful. I also attempted to use an Or to tack on an
additional conditional test telling it to append 0:00 if
[Stored_Sampler_Data]![Time]="2400" but that forced 0:00 into the
table for every time value. I am stumped. Anyone want to give a hand?

JV

 

[Jeff Boyce]

I can't tell from your description whether the value you are trying to
convert is a "point-in-time" or a "total number of hours&minutes".

If you are using the CDate() function, you will get a date ... but if your
input is 0001 - 2400, you don't have any "date" information, only time.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JAV]

I can't tell from your description whether the value you are trying to
convert is a "point-in-time" or a "total number of hours&minutes".

If you are using the CDate() function, you will get a date ... but if your
input is 0001 - 2400, you don't have any "date" information, only time.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello,

I have a database that I have been trying to convert into a correct
and recognizable time format. The old db kept time in numeric form
01...2400. The problem is when I try to import this into the new
database using CDate() it gives and error for each 2400 hour. I
understand that Access (2003) expresses 2400 hours as 0:00 but cannot
seem to get the right conversion for the append query. Here is what I
have for the query expression:

TimeValue: Date(IIf([Stored_Sampler_Data]![Time]<60,
(Format([Stored_Sampler_Data]![Time],"00@")),
(Format([Stored_Sampler_Data]![Time],"@@@"))))

I have attempted to use the switch function for choosing the case and
that was not successful. I also attempted to use an Or to tack on an
additional conditional test telling it to append 0:00 if
[Stored_Sampler_Data]![Time]="2400" but that forced 0:00 into the
table for every time value. I am stumped. Anyone want to give a hand?

JV

The value I am trying to convert is a point in time. It is downloaded
every other hour at twenty minute intervals but stored in the old db
in numeric form due to limitations of the device used to log the data.
For example the time column would contain 2400 for midnight, 20 for
12:20 AM, 40 for 12:40 AM, etc. Everything transfers over to time fine
using the CDate function except for the midnight value of 2400. I have
used the CDate function in the past to create time values without a
problem. In fact the TimeValue expression above works to convert to a
time for everything except the 2400 (midnight) value. Is there another
function I should be using? I have heard of the CTime function in C
but I am not aware of its availability in access.

 

[John W. Vinson]

The value I am trying to convert is a point in time. It is downloaded
every other hour at twenty minute intervals but stored in the old db
in numeric form due to limitations of the device used to log the data.
For example the time column would contain 2400 for midnight, 20 for
12:20 AM, 40 for 12:40 AM, etc. Everything transfers over to time fine
using the CDate function except for the midnight value of 2400. I have
used the CDate function in the past to create time values without a
problem. In fact the TimeValue expression above works to convert to a
time for everything except the 2400 (midnight) value. Is there another
function I should be using? I have heard of the CTime function in C
but I am not aware of its availability in access.

IIF([fieldname] = 2400, #00:00#, CDate([fieldname]))

John W. Vinson [MVP]

 

[John Spencer]

Or try

TimeValue(Format([TheField] mod 2400,"00:00"))

Of course, you might want to check for Null values or other data that cannot
be successfully converted.

IIF(IsDate(TimeValue(Format([TheField] mod 2400,"00:00"))),
TimeValue(Format([TheField] mod 2400,"00:00"))
,Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

The value I am trying to convert is a point in time. It is downloaded
every other hour at twenty minute intervals but stored in the old db
in numeric form due to limitations of the device used to log the data.
For example the time column would contain 2400 for midnight, 20 for
12:20 AM, 40 for 12:40 AM, etc. Everything transfers over to time fine
using the CDate function except for the midnight value of 2400. I have
used the CDate function in the past to create time values without a
problem. In fact the TimeValue expression above works to convert to a
time for everything except the 2400 (midnight) value. Is there another
function I should be using? I have heard of the CTime function in C
but I am not aware of its availability in access.

IIF([fieldname] = 2400, #00:00#, CDate([fieldname]))

John W. Vinson [MVP]