<div>
try MID(yourstr, instr(yourstr, &quot;+&quot;) + 1, (instr(yourstr, &quot;p&quot;) - instr(yourstr, &quot;+&quot;))-1)<br />
or something close.
</div>


<div>
try this<br />
<br />
Mid([Accrual Available Balance], InStr([Accrual Available Balance], &quot;+&quot;) + 1, InStrRev([Accrual Available Balance], &quot;p&quot;) - InStr([Accrual Available Balance], &quot;+&quot;) - 1)<br />
<br />
<br />
also I notice that the field [Accrual &nbsp; Available Balance] &nbsp;have two spaces between &quot;Acrual&quot; &amp;&nbsp;&quot;Available&quot; please verify this.
</div>


<div>
As you probably need the value as a decimal number, not a string, you can take advantage of this simple and direct method:<br />
<br />
&nbsp; &nbsp;ExtractedValue: Val(Mid([Accrual &nbsp; Available Balance], InStr([Accrual &nbsp; Available Balance], &quot;+&quot;)))<br />
<br />
/gustav
</div>


<div>
I use this code<br />

<pre><code class="code-1 nolinks" id="code-20-41424789-1"><span>Public Function ParseText(InputText As String, Delimiter As String, Part As Integer) As String</span>
<span>ParseText = Split(InputText, Delimiter)(Part)</span>
<span>End Function</span>
</code></pre>
<br />
So your code would be<br />

<pre><code class="code-1 nolinks" id="code-20-41424789-2"><span>-64.0(+40.08p) in order to get 40.08</span>
<span>result = parsetext(parsetext(-64.0(+40.08p),&quot;+&quot;,1),&quot;)&quot;,0)</span>
</code></pre>
</div>


<div>
CDBL(MID(yourstr, instr(yourstr, &quot;+&quot;) + 1, (instr(yourstr, &quot;p&quot;) - instr(yourstr, &quot;+&quot;))-1)) works
</div>


<div>
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.<br />
&nbsp;<br />
I have fields that will be blank and other that will have numbers as text. &nbsp;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”. &nbsp;Then the final if statement can contain the information.<br />
&nbsp;<br />
Here are some examples<br />
&nbsp;<br />
Data &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Desired Result &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;This is what I am currently receiving<br />
0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;#Func!<br />
1.83 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;#Func!<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;#Error<br />
0.0(+4.6p) &nbsp; &nbsp; &nbsp; 4.6 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4.6
</div>


<div>
Have not tried Coachman99 or John Tsioumpris' approach yet. &nbsp;Thanks all for the feedback. &nbsp;Updates to follow shortly...
</div>


<div>
include a column with criteria like this <br />
<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Instr([Accrual &nbsp; Available Balance], &quot;+&quot;)<br />
<br />
<br />
criteria &nbsp; &gt;0
</div>


<div>
post at <a href="#a41425566" rel="ugc">#a41425566</a>&nbsp;should resolve this problem
</div>


<div>
<a href="https://#a41425980" rel="ugc">https://#a41425980</a> does answer the question.<br />
<br />
/gustav
</div>


<div>
<div class="content wysiwyg-content">
Trying to extract text from access table...field info looks like this:<br />
<br />
Accrual &nbsp; Available Balance<br />
-64.0(+40.08p)<br />
0.0(+13.8p)<br />
0.0(+15.33p)<br />
<br />
Need to extract the data between the '+' and the 'p'. &nbsp;And not fixed placement. I tried the following formula:<br />
<br />
Right([Accrual &nbsp; Available Balance],Len([Accrual &nbsp; Available Balance])-InStr([Accrual &nbsp; Available Balance],&quot;+&quot;)) Left([Accrual &nbsp; Available Balance],Len([Accrual &nbsp; Available Balance]) + InStr([Accrual &nbsp; Available Balance],&quot;p&quot;))<br />
<br />
and kept getting an error that read 'The expression you entered contains invalid syntax, you may have entered an operand without an operator'.<br />
<br />
Can someone please help!!??!!
</div>
</div>


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

Access formula issue

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.