access query to grab a substring

I have a text field lets call it [field1] that contains this combination types of data that I will be using a query to grab:
30% ($25 min)
30% ($50 max)
30% ($25 min $150 max)
there will be no deviation it will be one of these 3 types.
I need to take the min and max data and place it into 2 different fileds.
I need to grab - when "min" the 25 (no dollar sign)
and when "max" grab the 50 or 150 (max can be 2 or 3 digits) no dollar sign.
I have the idea of finding when it exist using the like function so it would look like
Retail Cost Min: iif(like "*[Field1]*"="min",         , "")
Retail Cost Max: iif(like "*[Field1]*"="max",         , "")
but I don't know what to use to grab the exact data in the middle.
Any ideas anyone.
Stephen RoesnerAnalysisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
InStr() locates one string inside another and tells you where it found it.

Left() grabs the left most x characters.
Right() the right,

and Mid characters in the middle (start and length).

Since your fields are fixed, you can do:

IIF (Mid$([field],10,3)="MIN",

and so on.  Going to get terribly complex in the query with IIF()'s.  You should write a UDF (User Defined Function aka a procedure) in VBA to do it.

 And with all that said, the real answer is to redesign the DB.   You should not have multiple values in a field like that,


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Obviously someone will be able to solve this issue.

Ultimately this is two (or possibly three)  separate pieces of data, that should be stored separately...
With this more "Normalized " design, you will be allowed more flexibility if more Rate/Amount value combinations may be added.
Dale FyeOwner, Developing Solutions LLCCommented:
Agree with Jim.  If you are certain that all of the values of Field1 meet one of those formats, you could use this function:

Public Function fnMinMax(ParseWhat As String, Which As String) As Variant

    Dim myArray() As String
    myArray = Split(Replace(Replace(Replace(ParseWhat, "(", ""), "$", ""), ")", ""), " ")
    If myArray(2) = Which Then
        fnMinMax = myArray(1)
    ElseIf UBound(myArray) > 2 Then
        If myArray(4) = Which Then
            fnMinMax = myArray(3)
        End If
    End If

End Function

Open in new window

and call it like:
Retail Cost Min: fnMinMax([Field1], "min")
Retail Cost Min: fnMinMax([Field1], "max")
Stephen RoesnerAnalysisAuthor Commented:
Jim thanks for reminding me of Instr. I knew about left and mid and right but had forgotten about instr. I have used all 4 to get what I needed thank you very much for your assistance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.