asked on

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!!??!!

COACHMAN99

try MID(yourstr, instr(yourstr, "+") + 1, (instr(yourstr, "p") - instr(yourstr, "+"))-1)
or something close.

Rey Obrero (Capricorn1)

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 Brock

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

John Tsioumpris

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

COACHMAN99

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

Robert Cerrillo

ASKER

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 Cerrillo

ASKER

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

Robert Cerrillo

ASKER

or Gustov...

Rey Obrero (Capricorn1)

include a column with criteria like this

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

criteria >0

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.