text to decimals

[kevinwilson]

aloha all,
need some serious help here ...been stuck on this problem for two days now! here's my problem, i have a populated text field within my access mdb that i need to convert to a number field. the text field displays: 24:30, 6:45 (w/out leading 0's), etc., representing the total hours worked. i need to convert this field to a number field to include decimals ie 24.xx, 6.xx (xx=minutes) respectively, in order to sum the total hours+minutes worked. in the following example the "time1" displays total hours worked (h:m) and is formatted as text, i would like time2 formatted as a number (decimal,2). all attempts for force the text field into the number field returns either all 0's (0.00's), date/time's, or "type mismatch" errors. any help would be greatly appreciated.

no. time1(text) time2(num)
1 96:59 96.59
2 2:30 2.30

mucho mahalo's

 

[Golom]

Hmmm ... I don't thing that you will get the results you want from that. Shouldn't the conversion be

Text     Number

96:59     96.983
 2:30      2.50

If you add "96:59" + "2:30" you should get "99:29" but adding 96.59 + 2.30 gives 98.89.

Generating the "Hours" and "Minutes" fields is fairly easy.

Select Left([TextTime], Instr([TextTime],":")-1) As [Hours],
       Mid ([TextTime], Instr([TextTime],":")+1) As [Minutes]

Or Creating a single field

Select (Val(Left([TextTime], Instr([TextTime],":")-1)) +
        Val(Mid ([TextTime], Instr([TextTime],":")+1))/60) As [HrsMin]

 

[kevinwilson]

Golom, thanks for the quick reply. I understand the summing portion of your statement, this will help me alot once I can get the text field converted to number. In your second option:

Select (Val(Left([TextTime], Instr([TextTime],":&quot-1)) +
Val(Mid ([TextTime], Instr([TextTime],":&quot+1))/60) As [HrsMin]

Can the [HrsMin] be converter into a number field? I'm using a charting application where total hours worked must be must be a number field, date/time won't work.

thanks,
kevin

 

[Golom]

That construct DOES produce a number field (note the VAL functions.) Just to break it down

   Val(Left([TextTime], Instr([TextTime],":")-1))

Extracts the hours portion of the text field and converts it to a numeric value. Similarly

   Val(Mid ([TextTime], Instr([TextTime],":")+1))/60

Extracts the minutes, converts it to a number and divides by 60 to get fractions of an hour.

Finally

   Hours + Minutes

Adds the hours to the fraction of an hour derived from the minutes and the whole mess is contained in the field [HrsMin] as a numeric value of the form 2.50 (for example)

 

[kevinwilson]

D'oh, thanks for the clarification! Will give that a try.

mahalo,
kevin

 

[MichaelRed]

Public Function basTxtTime2Value(HrMin As String) As Double

    'Michael Red    Tex-Tips thread701-727708 TextTime to Decimal Time

    'Selected examples of usage
    '? basTxtTime2Value("24:30")
    ' 24.5

    '? basTxtTime2Value("96:59")
    ' 96.9833333333333

    '? basTxtTime2Value("6:45")
    ' 6.75

    Dim DecTime As Variant

    DecTime = Split(HrMin, ":")

    basTxtTime2Value = DecTime(0)
    If (UBound(DecTime) > 0) Then
        basTxtTime2Value = basTxtTime2Value + DecTime(1) / 60
    End If

End Function

On the other hand, a (hopefully) minor change to your app could allieviate the necessity for any of the above, although you can not 'store' hours > 23 in a date-time data type.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places