Extracting a portion of data

[sdzc]

I probably have an easy question for one of you out there, but I just cannot get it myself.

I need to extract the data between two characters in a string.

The data will be similar to this:

T4454: Text Text-Text: $296.07: Text Text

I need to get the dollar amount between the dollar sign and the colon on the right of it.

So far, I have this for my query:

Amount1: Mid$([subject],InStr([subject],"$"))

Which gets the amount to the left side, but also has the $ in it.

In the example above, I would like to just end up with 296.07 as the result from the query. I should also add that the dollar amount may vary from 1.00 to #,###.00

Thanks in advance for any help you can provide

 

[KeithG]

try this

Mid$([subject],(InStr([subject],"$")+1))

 

[sdzc]

That got rid of the dollar sign, but there is still text after the final number. There is a : after the last digit, if that helps to specify the info I want to extract.

 

[boblarson]

Try using this function:

Public Function GetValue(strText As String) As Currency
Dim curCurrency As Currency

    curCurrency = CCur(Mid(strText, InStr(1, strText, "$", vbTextCompare) + 1, (InStrRev(strText, ":", , vbTextCompare) - 1) - InStr(1, strText, "$", vbTextCompare)))
    GetValue = curCurrency
End Function

 

[sdzc]

Hate to ask this as it will show my true lack of Access skills, but where do I put the function?

 

[sdzc]

Thought I would bump this up to get any new opinions or some advice on the problem.

(12 people viewing the Queries area right now)