Extract Text (dollar amount) in Query

[red2002yzfr1]

I am looking to extract a dollar amount from a text box in a query. The dollar amount will reside in general text, "It cost $4,370 and was...." and can be anywhere within the text box. The dollar amount sometimes has cents. There will also be times that there will not be any dollar amount. Any help is greatly appriciated.

 

[MSAccessRookie]

I am looking to extract a dollar amount from a text box in a query. The dollar amount will reside in general text, "It cost $4,370 and was...." and can be anywhere within the text box. The dollar amount sometimes has cents. There will also be times that there will not be any dollar amount. Any help is greatly appriciated.

As long as the dollar amount has a standardized form similar to the one below, the task should be easy. You just locate the "$" and select everything between it and the next space.

• It will cost $XXX,XXX.XX Dollars

WHERE

RED is Required Data
PURPLE is Optional Data
Black is text to Ignore

Note: This Model will cover values from $1 to $999,999.99. Modify as necessary for additional values.

 

[raskew]

Hi -

Provided there is only one set of numbers in your string, this should do it:

Function SaveNumer(ByVal pStr As String) As String
'*******************************************
'Purpose:   Removes alpha characters from a string
'Coded by:  raskew
'Calls:     Function IsNumeric()
'Inputs:    ? SaveNumer(" t#he *qu^i5ck !b@r#o$w&n 4fo#x ")
'Output:    5   4
'Note:      As written, empty spaces are ignored.
'*******************************************

Dim strHold As String
Dim intLen  As Integer
Dim n       As Integer

    strHold = Trim(pStr)
    intLen = Len(strHold)
    n = 1
    Do
       If Mid(strHold, n, 1) <> " " And Not IsNumeric(Mid(strHold, n, 1)) [COLOR="Red"]And Mid(strHold, n, 1) <> "."[/COLOR] Then
          strHold = Left(strHold, n - 1) + Mid(strHold, n + 1)
          n = n - 1
       End If
       n = n + 1
    Loop Until Mid(strHold, n, 1) = ""
    SaveNumer = strHold
    
End Function

Testing, from the debug (immediate) window:

x= "It cost $4,370 and was...."
? savenumer(x)
4370

Note that this returns a string. To return a number, wrap the above in the val() function

? val(savenumer(x))
4370

HTH - Bob

Added:

Just reread your post and noted that the amount could include cents. Have modified the code to allow for that.

 

[MSAccessRookie]

Hi -

Provided there is only one set of numbers in your string, this should do it:

Function SaveNumer(ByVal pStr As String) As String
'*******************************************
'Purpose:   Removes alpha characters from a string
'Coded by:  raskew
'Calls:     Function IsNumeric()
'Inputs:    ? SaveNumer(" t#he *qu^i5ck !b@r#o$w&n 4fo#x ")
'Output:    5   4
'Note:      As written, empty spaces are ignored.
'*******************************************
 
Dim strHold As String
Dim intLen  As Integer
Dim n       As Integer
 
    strHold = Trim(pStr)
    intLen = Len(strHold)
    n = 1
    Do
       If Mid(strHold, n, 1) <> " " And Not IsNumeric(Mid(strHold, n, 1)) [COLOR=red]And Mid(strHold, n, 1) <> "."[/COLOR] Then
          strHold = Left(strHold, n - 1) + Mid(strHold, n + 1)
          n = n - 1
       End If
       n = n + 1
    Loop Until Mid(strHold, n, 1) = ""
    SaveNumer = strHold
 
End Function

Testing, from the debug (immediate) window:

x= "It cost $4,370 and was...."
? savenumer(x)
4370

Note that this returns a string. To return a number, wrap the above in the val() function

? val(savenumer(x))
4370

HTH - Bob

Added:

Just reread your post and noted that the amount could include cents. Have modified the code to allow for that.

Bob,

Your code example is a great learning tool, but what happens in the case below?

pStr = "The cost of 1 Item was $4,370, while 2 items were $7,890."

Afterthought:

Or any other case where there is a number or a "." in the additional text

 

[raskew]

pStr = "The cost of 1 Item was $4,370, while 2 items were $7,890."

Good point! The case cited I'd probably go with a modification of your suggestion. But, even that presents problems if starting with "$" thru the first space. Example: $4,370, $7,890.

Have to give that some more thought. It could get ugly.

Best Wishes - Bob