Converting Number To Time

[Jeff Garrison]

Hello all -

I'm hitting a SQL backend database that has a time field in it, but it's
store as numbers. Below is some of the data it contains:

Entered Required Start Complete
675 615 675 676 data
11:15 10:15 11:15 11:16 time

802 742 802 804 data
13:22 12:22 13:22 13:24 time

897 837 897 898 data
14:57 13:57 14:57 14:58 time

I can't seem to figure out how to change the decimal back to the correct
time.

Any help would be GREATLY appreciated.

Thanks in advance!

JeffG

 

[Evi]

I don't see any decimals, only whole numbers and times

You can turn stuff into a normal time using the TimeValue function.

Evi

 

[Evi]

Now if the numbers were
11.15 and you wanted it to say
11:15 then you could use this

MyTime: TimeValue(Left([YourField],InStr([YourField],".")-1) & ":" &
Mid([YourField],InStr([YourField],".")+1))

PheW!

Evi

 

[John Spencer]

It appears that you are storing the time as the number of minutes since
midnight. If so, then the following should generate the time for you.

DateAdd("n",[SomeFieldName],#00:00:00#)

You can probably get away with
DateAdd("n",[SomeFieldName],0)
since Access will probably convert the zero to #00:00:00# internally.

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

 

[Jeff Garrison]

The whole numbers are what is being stored in the SQL table. The time is
what the actual time it should come back to.

 

[Evi]

Oh, I see, its not really a time, 675 is 11 hours and 15 minutes
=((YourNum/60)-INT(YourNum/60))*60 gives you the 15 minutes
Int(YourNumber/60) gives you the 11
so to put it into a time you would need the following row in a query - use
this as an update query for your table but keep your time numbers until you
are sure they are ok
MyTime:TimeValue(Int(YourNumber/60) & ":" &
((YourNum/60)-INT(YourNum/60))*60 )

Evi

 

[Evi]

Or if this really isn't a time field at all, but a way of displaying a
collection of hours and minutes, you would better still to store the minutes
so that you can do any arithmetic and keep the MyTime function only for
display purposes.

As far as I know Access doesn't have the same method of adding hours and
minutes time as Excel's [hh]:mm format and so won't allow you to add up your
times directly

Evi