How do I capture part of a text string?

S

sarahhb

With a text string like this:

Total for CO-SEMH/CA - 33030 with Mode 15 and SFC 58 8 530 $1,054.70

How do I capture just the numbers following a $ sign? They vary in length,
so I cannot use simply the Right function. The db is currently set to do this
in the "field" section of the query.

thank you.
 
O

Ofer

If the number always in the end of the string, then try this

'To include the $ sign
mid(FieldName , instr(FieldName,"$")-1)

'Just the number
mid(FieldName , instr(FieldName,"$"))
 
S

sarahhb

Thanks Ofer. Another question- how would I capture the numbers to the left of
the $ sign? In this case, just the number 530.

I really appreciate your help- I have been struggling with this!
 
O

Ofer

I can't see any other way but using code
Create a function that Get a string and return the number

Try this

Function FunctionName(MyString as string)
Dim I as Integer
If MyString <> "" and not isnull(MyString ) then
For I=1 to Len(MyString )
If trim(Mid(MyString , instr(MyString ,"$") - I,I))<> "" then
If IsNumeric(Mid(MyString , instr(MyString ,"$") - I,I)) then
FunctionName = Cdbl(Mid(MyString , instr(MyString
,"$") - I,I))
Else
Exit Function
End If
End If
Nexi I
end if
End Function
 
S

sarahhb

Ofer-
Again, I really appreciate your help. I haven't been able to focus on this
issue, and am just now trying to resolve it. I am having trouble following
your directions because I have no experience with Visual Basic. For example,
I have no idea how to name MyString. I think I want to refer to a column in a
table, but am unsure what the proper way to go about this would be. Also, in
the code you suggested, you included "End If
Nexi I
End If
End Function"

Should that be "Next I"?

Thanks. Is there a resource that would help me understand these basics?
Sarah
 
Top