SQL to eliminate the text and keep the number, capturing the number in hours

Question

I have a table called VISIT REPORT. There is one column in the report named, Hours Spent, which I want to make changes.

Due to imported data from other sources, some records for Hours Spent is recorded as, for example, "1 hour", "2 minutes", "3 days". I want to eliminate the text and keep the number, capturing the number in hours. How do I do it since there are three different units?

This is my code to identify those records that contains "hour", "minute", "day" and even terms like "hr", "min", "d" etc

SELECT *
FROM [VISIT REPORT]
WHERE ((([VISIT REPORT].[Hours Spent]) Like '*h*' Or ([VISIT REPORT].[Hours Spent]) Like '*m*' Or ([VISIT REPORT].[Hours Spent]) Like '*d*'));

I am stuck here. I tried adding this code in, which I believe is wrong, since it does not take note of the three different units, hours, minutes and days.

UPDATE VISIT REPORT SET [Hours Spent] = Val([Hours Spent])

Now I separated the problem into three queries and started off with one query to fix the fields for hour first. - It works!

First create a module,

Function RemoveAlphas(ByVal AlphaNum As Variant)

   Dim Clean As String
   Dim Pos, A_Char$

   Pos = 1
   If IsNull(AlphaNum) Then Exit Function

   For Pos = 1 To Len(AlphaNum)
      A_Char$ = Mid(AlphaNum, Pos, 1)
      If A_Char$ >= "0" And A_Char$ <= "9" Then
         Clean$ = Clean$ + A_Char$
      End If
   Next Pos

   RemoveAlphas = Clean$

End Function

Then insert this SQL,

UPDATE [VISIT REPORT] SET [VISIT REPORT].[Hours Spent] = "RemoveAlphas[Hours Spent]"
WHERE ((([VISIT REPORT].[Hours Spent]) Like '*h*'));

Still trying to figure out how to solve the fields for minutes and days.

Answer 1

Val([Hours Spent]) will give you the initial number. Then you can use a Switch expression to determine a multiplication factor to express the initial number in units of hours.

UPDATE [VISIT REPORT]
SET [Hours Spent] = 
    CStr
        (
            Val([Hours Spent])
            *
            Switch
                (
                    [Hours Spent] Like '*day*', 24,
                    [Hours Spent] Like '*hour*', 1,
                    [Hours Spent] Like '*min*', (1/60)
                )
        )
WHERE [Hours Spent] Like '*[dhm]*';

Here is a table which shows your sample values, and their computed equivalents in hours:

Hours Spent replacement text (hours)
----------- ------------------------
1 hour      1
2 minutes   3.33333333333333E-02
3 days      72

Although this gives you what you requested, I have misgivings about it because storing numbers as text is seldom the best choice. Whenever you want to work with those text values as numbers, you will have to cast them accordingly. In such situations, your queries will not be able to take advantage of indexing on [Hours Spent], which means poorer performance. I would prefer to store the computed hours in a separate field whose datatype is numeric (Single or Double).