Text conversion to number

[Guest]

My Access database includes a link to a SQL Server database. One of their
fields contains hours and minutes in an hh:mm format, e.g., 25:47. Their
field is a varchar field which means mine shows up as a text field. Here's
my problem: I've written a query generator that allows me to generate
reports, but because I'm stuck with this value as a text field, it's
difficult to do numerical comparisons. Any ideas? I could select all
records from this table and do an INSERT into a new table, but I'm still
faced with the problem of converting this value into a number. Thanks.

 

[Guest]

You are definitely suffering from some else's bad design. Had they been
thinking at all, it would be a long or integer numberic type and they would
carry it is total minutes so it would be 1547 instead of 25:47, but nothing
you can do about that now. You may consider writing a function that will
convert the text value to a number so you can do the numeric compares. Then
use a query against the table instead of the table itself. The function
might look like this:

Public Function TimeInMinutes(strTextTime As Sring) As Long
Dim varHrsAndMins As Variant

varHrsAndMinutes = Split(strTextTime, ";")
TimeInMinutes = (varHrsAndMinutes(0) * 60) + varHrsAndMinutes(1)
End Function

The in the query

NumTime: TimeInMinutes([GoofyTextField])