conversion

[cochise]

I have a field that has a string in the following format:

hours:minutes, for example 58:14, for 58 hours and 14 minutes.

Because the hours exceed 24, I can't use the time format. How can I convert this string into an number?

 

[MikiH]

You could add two boxes, one for hours & one for minutes. Then hide the original box and then "after update" of the two new boxes add them together ie

after update

oldbox = hoursbox + (minutesbox/60)


Hope this helps.

 

[cochise]

It's actually not an entry field on a form. The data is imported from another database in this format. I was thinking about converting in a query possibly by creating some code.

 

[MikiH]

Hi

I know you can pick up the fist and last parts of a text string, cannot think of the keyword I'll have a quick search and get back to you in 5.

 

[MikiH]

hope this helps its straight from access help.

you could calculate the hours in one column, minutes in the next then add them togther. It might need a query on a query?


Mick



Extract part of existing text values using a calculated field

1 In query Design view, create a query. Add the tables whose records you want to use.
2 To find values in part of a field, use the Left, Right, or Mid function in an expression in an empty cell in the Field row in the query design grid.

The syntax for these functions is:

Left(stringexpr,n)
Right(stringexpr,n)
Mid(stringexpr,start,n)

The stringexpr argument can be either a field name (enclosed in brackets) or a text expression; n is the number of characters you want to extract; start is the position of the first character you want to extract.

The following table shows examples of these functions.

If the value in
Part ID is
This expression
Returns
BA-7893-R12 Left([PartID],2) BA
BA-7893-R12 Right([PartID],3) R12
BA-7893-R12 Mid([PartID],4,4) 7893
3 Add any other fields you want to include in the query to the query design grid.