How do I capture part of a text string?

[Guest]

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.

 

[Guest]

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,"$"))

 

[Guest]

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!

 

[Guest]

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

 

[Guest]

Thanks Ofer. Can I utilize this code by creating a new module?
How do I name the Function?

 

[Guest]

Create a new module, copy and paste the code I Provided, you can give the
function any name that you want as long that you are not using something you
used before, or a reserved name by Access like (date, year ....)
=============================================
To try the function, open the immidiate window (press ctrl+g) type in it

?TheNameOfTheFunction("CO-SEMH/CA - 33030 with Mode 15 and SFC 58 8 530
$1,054.70")

press enter, it should display the number on the left to the $ sign.
==============================================
You can use the function to display the value in a query, or in a field in a
report or form.

 

[Guest]

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