Time Conversion

[BPhilb]

I'm stumped and not sure why this is happening. I have a time field from an AS400 table which comes in the format of 80000 for 8:00 a.m. and 130000 for 1:00 p.m.. I'm trying to do a simple conversion to make these numbers appears as a time. I have a function that should handle the conversion and it appears to run through fine but the time field shows up blank. My function is:

Function MkeTime
Dim db As Database
Dim rst As Recordset
Dim fld As Field
Dim f As Variant
Set db = CurrentDb()

If n < 99999 Then
f = Format(Left([n], 1) & &quot;:&quot; & Mid([n], 2, 2) & &quot;:&quot; & Right([n], 2))
n = f
Else
f = Format(Left([n], 2) & &quot;:&quot; & Mid([n], 3, 2) & &quot;:&quot; & Right([n], 2))
n = f
End If
End Function

I have also tried making a table and then converting the numbers into a new field and I get the same results. If anyone has any ideas I would appreciate any help. Thanks in advance.

Brad

 

[combo]

First check, if you deal with string or number.
If it a string, then use:
[tt]MkeTime=TimeValue(Left([n],Len[n]-4) & &quot;:&quot; & Mid([n],Len([n]-3,2) & &quot;:&quot; & Right([n],2)[/tt]
Use Trim([n]) if you can have spaces.

If [n] is a number, use:
[tt]MkeTime=TimeSerial(Int([n]/10000,Int(([n] Mod 10000)/100),[n] Mod 100)[/tt]

combo

 

[BPhilb]

Combo,


I was converting an integer and your suggestion worked beautifully. Thanks so much for you help.

Brad

 

[combo]

Hi Brad,
I missed some brackets, should be (for number):
MkeTime=TimeSerial(Int([n]/10000),Int(([n] Mod 10000)/100),[n] Mod 100)

and for string:
MkeTime=TimeValue(Left([n],Len([n])-4) & &quot;:&quot; & Mid([n],Len([n])-3,2) & &quot;:&quot; & Right([n],2))

Hope you noticed

combo