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!
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!!??!!
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!!??!!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try MID(yourstr, instr(yourstr, "+") + 1, (instr(yourstr, "p") - instr(yourstr, "+"))-1)
or something close.
or something close.
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.
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.
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
ExtractedValue: Val(Mid([Accrual Available Balance], InStr([Accrual Available Balance], "+")))
/gustav
I use this code
So your code would be
Public Function ParseText(InputText As String, Delimiter As String, Part As Integer) As String
ParseText = Split(InputText, Delimiter)(Part)
End Function
So your code would be
-64.0(+40.08p) in order to get 40.08
result = parsetext(parsetext(-64.0(+40.08p),"+",1),")",0)
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
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
Have not tried Coachman99 or John Tsioumpris' approach yet. Thanks all for the feedback. Updates to follow shortly...
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)
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)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial