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.
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!!??!!
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.
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)
Still it can still be done extremely simple:
Null 0
"" 0
"0" 0
"1.83" 0
"0.0(+4.6p)" 4.6
/gustav
ExtractedValue: Val(Mid(Nz([Accrual Available Balance]), InStr([Accrual Available Balance] & "+", "+")))
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
post at #a41425566 should resolve this problem
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
or something close.