Access formula issue

Robert Cerrillo
Robert Cerrillo used Ask the Experts™
on
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!!??!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try MID(yourstr, instr(yourstr, "+") + 1, (instr(yourstr, "p") - instr(yourstr, "+"))-1)
or something close.
Top Expert 2016

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.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

John TsioumprisSoftware & Systems Engineer

Commented:
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

CDBL(MID(yourstr, instr(yourstr, "+") + 1, (instr(yourstr, "p") - instr(yourstr, "+"))-1)) works

Author

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

Author

Commented:
Have not tried Coachman99 or John Tsioumpris' approach yet.  Thanks all for the feedback.  Updates to follow shortly...

Author

Commented:
or Gustov...
Top Expert 2016

Commented:
include a column with criteria like this

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


criteria   >0
Top Expert 2016
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)
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
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
Top Expert 2016

Commented:
post at #a41425566 should resolve this problem
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
https://#a41425980 does answer the question.

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial