<div>
<div class="content wysiwyg-content">
I have the following codes in VBA. Can anyone tell me where goes wrong? I guess it's probably in the Mid function in the SQL statement.<br />
<br />
With CurrentDb.OpenRecordset(&quot;S<wbr />ELECT tbl_A.* FROM tbl_A INNER JOIN tbl_B ON &quot;&nbsp;_<br />
&amp;&nbsp;&quot; ( Mid(tlb_A.aFieldThatContai<wbr />nsCaseID, 58, 5) = str(tbl_B.caseID) ) WHERE tbl_A.fld_1 IS NOT NULL&quot;)<br />
&nbsp; While Not .EOF<br />
&nbsp; existVal = Mid(.Fields(&quot;aFieldThatCon<wbr />tainsCaseI<wbr />D&quot;), 58, 5) &amp;&nbsp;&quot; &quot;&nbsp;&amp; existVal<br />
&nbsp; .MoveNext<br />
&nbsp; Wend<br />
.Close<br />
End With<br />
&nbsp;<br />
Open in New Window 
</div>
</div>


I have the following codes in VBA. Can anyone tell me where goes wrong? I guess it's probably in the Mid function in the SQL statement.

With CurrentDb.OpenRecordset("SELECT tbl_A.* FROM tbl_A INNER JOIN tbl_B ON " _
& " ( Mid(tlb_A.aFieldThatContainsCaseID, 58, 5) = str(tbl_B.caseID) ) WHERE tbl_A.fld_1 IS NOT NULL")
  While Not .EOF
  existVal = Mid(.Fields("aFieldThatContainsCaseID"), 58, 5) & " " & existVal
  .MoveNext
  Wend
.Close
End With
 
Open in New Window 

How to use Mid function in SQL statement, anyone can help?

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.