Solved

How to use Mid function in SQL statement?

Posted on 2008-06-10
5
1,071 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]
Comment Utility
in sql server, the "mid" function is calls substring.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 85 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
The problem is solved by using Val to convert whatever Mid function returns to numbers.

Thank you guys.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

743 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

14 Experts available now in Live!

Get 1:1 Help Now