if with a val

[imintrouble]

Hello Forums, long time no post!

I need a way to say, If this string contains "Hours" then take the numbers in front of it with a val() function. If it does not contain "Hours" in the string, it needs to ignore this by doing nothing. This will be ran in a query which i am making a report off of. The number of "Hours" will then be saved into a seperate field in the query. so basiaclly



IF ([STRING] = "Hours")
[NEWFIELD] = val([STRING])
ELSE
DO NOTHING
ENDIF

something like this

[boblarson]

If Instr(1, [STRING], "Hours") > 0 Then
   [NEWFIELD] = val([STRING])
End If

You don't need the Else either.

[imintrouble]

Thanks Bob.

I put the code in as a function and called it into the new field. However after i compiled it and went back to the query to see if it had worked, instead of having the rows that had Hours included in the strings number value shown, ever cell in the new field was displaying 0. This wouldn't be so bad if it would have still gotten the right number from the string in the first place.

[boblarson]

What does the string look like originally? Post an example.

[imintrouble]

here are a couple of examples of what my string may look like.

3 Minutes, 39 Seconds
1 Minute, 42 Seconds

3 Hours, 6 Minutes, 26 Seconds
1 Hour, 3 Minutes, 25 Seconds

These are two sets of examples from my actual query I am using currently.

[RayMilhon]

If the hours are always first when there I would use the following
If Instr(1,[String],"Hours) > 0 Then
'2 possibilities either should work
[Newfield]=Val(left([String],Instr(1,[string],",")-1))
'Or
[Newfield] = Val(left([string],Instr(1,[string],"Hours")))
End if

[imintrouble]

how do i get the newfield into this code? i tried calling it as an argument, but because this code is being used inside of the new field i am making, it is secular, using itself for the code. which apparently it isn't able to do. hmm...

[imintrouble]

this is the code I have so far, it still only returns 0 though,

Public Function GetTimeString(strHours As String) As Integer
 
    If InStr(1, strHours, "Hours") > 0 Then
        strHours = Val(Left(strHours, InStr(1, strHours, ",") - 1))
    End If
    
End Function

the strHours is the field where the original "3 Hours, 26 Minutes, 13 Seconds" is going to be.

[RayMilhon]

first thing to test is put a breakpoint on the strhours = Line. When the code stops in the immediate Window put ?Left(strHours,Instr(1,strhours,",")-1)) and see what you're getting If you're not getting the "3 Hours" then look at the entire string and make sure what's there.

Post back your answers

[imintrouble]

Soooooo...after changing my code to this:

   If InStr(strHours, "Hour") > 0 Then
       strHours = Val(Left(strHours, InStr(1, strHours, ",") - 1))
    End If
    
    GetTimeString = strHours

anything in the field that had "hour" in it, reported back something like this,

original: 3 Hours, 4 Minutes
New field: 3

However the datafields that did not have hours but only minutes or seconds had this

original: 3 Minutes, 39 Seconds
New field: 3 Minutes, 39 Seconds

but now my new field is as a string, which means i'm probably going to have to convert them using a val function to get them as integers to be added on my report for total hours. But first things first, i need tthese fields that are reporting only the minutes and seconds to be blank.

[RayMilhon]

Sorry, I had my stupid moment of the day. went back and Reread your code here's the change you need to make
change the strHours = Val(....
To: GetTimeString = Val(...

Also after the GettimeString = Val(.... Line add the following 2 lines
Else
Gettimestring = 0

Remove the gettimestring = strhours

[boblarson]

Just an FYI. I thought you were wanting the NUMBER of hours. Not text returned of 3 Hours. VAL() takes the NUMBER ONLY so you just want to pull out the rest. To make it really easy put this function into a STANDARD MODULE and then save it as basTime and then call it from your query.

Function GetTimePart(strInput As String, strPart As String, Optional strDelimiter As String) As String
    Dim strDelim As String
    Dim varSplit As Variant
    Dim i As Integer
 
On Error GoTo Errors
    If strDelimiter = vbNullString Then
        strDelim = ","
    Else
        strDelim = strDelimiter
    End If
 
    varSplit = Split(strInput, strDelim)
 
    For i = 0 To UBound(varSplit)
        If InStr(1, varSplit(i), strPart) > 0 Then
           GetTimePart = varSplit(i)
           Exit For
        End If
    Next
 
    If GetTimePart = vbNullString Then
       GetTimePart = "Not Found"
    End If
 
ExitHere:
     Exit Function
Errors:
 
            MsgBox "Error " & Err.Number & " - " & " (" & Err.Description & ") in procedure GetTimePart"
            Resume ExitHere
            Resume
 
End Function

And then just call it from code like this:

   If Instr(1, [STRING], "Hours") > 0 Then
      [NEWFIELD] = GetTimePart([STRING], "Hours")
   End If

[imintrouble]

Thank you to all who have helped. You two fine men have earned yourselves some extra reputation . Again, thanks!