?
Solved

How to use Mid function in SQL statement?

Posted on 2008-06-10
5
Medium Priority
?
1,139 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 143

Expert Comment

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

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 255 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 120 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

862 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