Solved

How to use Mid function in SQL statement?

Posted on 2008-06-10
5
1,078 Views
Last Modified: 2013-11-27
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?
0
Comment
Question by:angie_angie
  • 2
  • 2
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21749841
in sql server, the "mid" function is calls substring.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 85 total points
ID: 21749846
sorry, drop that ;)


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

0
 

Author Comment

by:angie_angie
ID: 21756156
angellll,

I tried your codes... Although no error shows, there is nothing written to the string existVal.

Can you help?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 40 total points
ID: 21756634
angie_angie,

First, ...Is the actual field name: "aFieldThatContainsCaseID"  ???
If not, then that might be your problem.

Second, I would check to see, what, if anything is being returned by the Mid() function.

So I would make a simple form from the table, with only the "aFieldThatContainsCaseID" Field.
I would drop a second textbox on the form, and make the controlsource:
=Mid([aFieldThatContainsCaseID], 58, 5)

Now just cycle through the records, and make sure that the correct value is displayed in the second textbox (For all the records).

JeffCoachman
0
 

Author Comment

by:angie_angie
ID: 21757395
The problem is solved by using Val to convert whatever Mid function returns to numbers.

Thank you guys.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now