Need to pull max price out of paragraph

[WSUAccess]

So far it finds the "$", then returns the string. Occasionally there is a $5k, so it also changes it to $5000 and returns it. Now I need help if there are multiple values (always preceded by a "$") and pulling the max. Thanks in Advance, as I can't seem to figure this one out.


Public Function DollarAmt(s As String) As Double
If InStr(s, "$") Then
s = Mid(s, InStr(s, "$") + 1)
If Mid(s, Len(Trim(Val(s))) + 1, 1) = "k" Then
s = Val(s) & "000"
Else
s = Val(Replace(s, ",", ""))
End If
Else
s = 0
End If
DollarAmt = s
End Function

 

[bwellbor]

There may be other ways of doing this, but I played around with it for a minute using a loop and it seemed to do what you're looking to do. Have a look.

Public Function DollarAmt(s As String) As Double

Dim find_dollar_sign As Integer
Dim amount_value As String
Dim max_amount As Single

'Set this variable to 1. This is the position in the string s where we'll start to look for the $
find_dollar_sign = 1

Do
    
    'If the $ is found
    If InStr(find_dollar_sign, s, "$") Then
        
        'Find the amount
        amount_value = Mid(s, InStr(find_dollar_sign, s, "$") + 1)
        
        
        If Mid(amount_value, Len(Trim(Val(amount_value))) + 1, 1) = "k" Then
            amount_value = Val(amount_value) & "000"
        Else
            amount_value = Val(Replace(amount_value, ",", ""))
        End If
        
        'Set this variable to the position where the previous $ was found, plus add 1 so we don't find the previous $ again
        find_dollar_sign = InStr(find_dollar_sign, s, "$") + 1
        
    'If the $ isn't found
    Else
        
        'Set the amount to 0 and exit the loop
        amount_value = 0
        Exit Do
        
    End If
    
    'If the amount is greater than the current max_amount, set a new max_amount
    If Val(amount_value) >= max_amount Then max_amount = Val(amount_value)

Loop

DollarAmt = max_amount

End Function

 

[WSUAccess]

Wow, it works great!!! Thanks so much for your help. This is perfect.

 

[WSUAccess]

One other question if you have time. I just realized my original query is setup to pull $1k, then swap the k with ",000". But not for $10k senarios. I tried to add an "OR" at the beggining and change the +1 to +2, but that didn't seem to work. Any suggestions? Thanks so much as I'm still very new at this.


If Mid(amount_value, Len(Trim(Val(amount_value))) + 1, 1) = "k" Then
amount_value = Val(amount_value) & "000"
Else
amount_value = Val(Replace(amount_value, ",", ""))
End If

 

[bwellbor]

I think what you've got should work for $10k, $100k, whatever. Is it not working?

 

[vbaInet]

For this to make perfect sense, you need to give us some examples and what you expect to see.

 

[WSUAccess]

My apologies bwellbor and vbaInet, I just realized I'm an idiot. The reason my query isn't working is due to some entries having lower case "k" and some upper case "K". Pretty much an exhaustive list of values I need to pull would be:

$10 / $100 / $1k / $1K / $1000 / $1,000 / $10000 / $10k /.....

 

[vbaInet]

It shouldn't make any difference really.

Have you got Option Compare Database in the declarations section of your module?

If the function was using StrComp() with the Binary option then I would expect this kind of behaviour.

 

[WSUAccess]

That works great!! I did not have the Option Compare Database in the declarations. Thank you so much. I can't even describe how much I appreciate your help.

 

[vbaInet]

Don't forget to put Options Explicit right under that. Then also remember to Debug > Compile your database.

Happy developing!