<div>
in sql server, the &quot;mid&quot; function is calls substring.
</div>


<div>
angellll,<br />
<br />
I tried your codes... Although no error shows, there is nothing written to the string existVal.<br />
<br />
Can you help?
</div>


<div>
The problem is solved by using Val to convert whatever Mid function returns to numbers. <br />
<br />
Thank you guys.
</div>


<div>
<div class="content wysiwyg-content">
I have the following codes that look for the common records of two tables and join the caseIDs for these common records as one string:<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.aFieldThatConta<wbr />insCaseID,<wbr /> 58, 5) = 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 />
<br />
But error occurs saying type mismatch. I think the problem lies in the Mid function in the SQL statement. Can anyone suggest a way out?
</div>
</div>


I have the following codes that look for the common records of two tables and join the caseIDs for these common records as one string:

With CurrentDb.OpenRecordset("SELECT tbl_A.* FROM tbl_A INNER JOIN tbl_B ON " _
& "Mid(tlb_A.aFieldThatContainsCaseID, 58, 5) = 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

But error occurs saying type mismatch. I think the problem lies in the Mid function in the SQL statement. Can anyone suggest a way out?

in sql server, the &quot;mid&quot; function is calls substring.

angellll,

I tried your codes... Although no error shows, there is nothing written to the string existVal.

Can you help?

The problem is solved by using Val to convert whatever Mid function returns to numbers. 

Thank you guys.

How to use Mid function in SQL statement?

I have the following codes that look for the common records of two tables and join the caseIDs for these common records as one string:

With CurrentDb.OpenRecordset(&quot;SELECT tbl_A.* FROM tbl_A INNER JOIN tbl_B ON &quot; _
&amp; &quot;Mid(tlb_A.aFieldThatContainsCaseID, 58, 5) = tbl_B.caseID WHERE tbl_A.fld_1 IS NOT NULL&quot;)
  While Not .EOF
  existVal = Mid(.Fields(&quot;aFieldThatContainsCaseID&quot;), 58, 5) &amp; &quot; &quot; &amp; existVal
  .MoveNext
  Wend
.Close
End With

But error occurs saying type mismatch. I think the problem lies in the Mid function in the SQL statement. Can anyone suggest a way out?

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.