Access formula issue

Trying to extract text from access table...field info looks like this:

Accrual   Available Balance
-64.0(+40.08p)
0.0(+13.8p)
0.0(+15.33p)

Need to extract the data between the '+' and the 'p'.  And not fixed placement. I tried the following formula:

Right([Accrual   Available Balance],Len([Accrual   Available Balance])-InStr([Accrual   Available Balance],"+")) Left([Accrual   Available Balance],Len([Accrual   Available Balance]) + InStr([Accrual   Available Balance],"p"))

and kept getting an error that read 'The expression you entered contains invalid syntax, you may have entered an operand without an operator'.

Can someone please help!!??!!
Robert CerrilloAsked:
Who is Participating?
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.

COACHMAN99Commented:
try MID(yourstr, instr(yourstr, "+") + 1, (instr(yourstr, "p") - instr(yourstr, "+"))-1)
or something close.
Rey Obrero (Capricorn1)Commented:
try this

Mid([Accrual Available Balance], InStr([Accrual Available Balance], "+") + 1, InStrRev([Accrual Available Balance], "p") - InStr([Accrual Available Balance], "+") - 1)


also I notice that the field [Accrual   Available Balance]  have two spaces between "Acrual" & "Available" please verify this.
Gustav BrockCIOCommented:
As you probably need the value as a decimal number, not a string, you can take advantage of this simple and direct method:

   ExtractedValue: Val(Mid([Accrual   Available Balance], InStr([Accrual   Available Balance], "+")))

/gustav
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

John TsioumprisSoftware & Systems EngineerCommented:
I use this code
Public Function ParseText(InputText As String, Delimiter As String, Part As Integer) As String
ParseText = Split(InputText, Delimiter)(Part)
End Function

Open in new window


So your code would be
-64.0(+40.08p) in order to get 40.08
result = parsetext(parsetext(-64.0(+40.08p),"+",1),")",0)

Open in new window

COACHMAN99Commented:
CDBL(MID(yourstr, instr(yourstr, "+") + 1, (instr(yourstr, "p") - instr(yourstr, "+"))-1)) works
Robert CerrilloAuthor Commented:
Rey - The formula works however, I have other data in the field that the formula doesn’t like that I need to account for in the formula.
 
I have fields that will be blank and other that will have numbers as text.  What I believe I need is a statement that will first look for blank fields and return a “0” and then look for a “p” if the field does not contain a “p” then I need a “0”.  Then the final if statement can contain the information.
 
Here are some examples
 
Data                Desired Result            This is what I am currently receiving
0                      0                                  #Func!
1.83                 0                                  #Func!
                        0                                  #Error
0.0(+4.6p)       4.6                               4.6
Robert CerrilloAuthor Commented:
Have not tried Coachman99 or John Tsioumpris' approach yet.  Thanks all for the feedback.  Updates to follow shortly...
Robert CerrilloAuthor Commented:
or Gustov...
Rey Obrero (Capricorn1)Commented:
include a column with criteria like this

            Instr([Accrual   Available Balance], "+")


criteria   >0
Rey Obrero (Capricorn1)Commented:
try this

IIF(Instr([Accrual Available Balance],"+")>0, Mid([Accrual Available Balance], InStr([Accrual Available Balance], "+") + 1, InStrRev([Accrual Available Balance], "p") - InStr([Accrual Available Balance], "+") - 1),0)
Gustav BrockCIOCommented:
Still it can still be done extremely simple:
ExtractedValue: Val(Mid(Nz([Accrual   Available Balance]), InStr([Accrual   Available Balance] & "+", "+")))

Open in new window

Input                 Output
Null                   0
""                       0
"0"                     0
"1.83"                0
"0.0(+4.6p)"     4.6

/gustav

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
Rey Obrero (Capricorn1)Commented:
post at #a41425566 should resolve this problem
Gustav BrockCIOCommented:
https://#a41425980 does answer the question.

/gustav
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.