Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to use Mid function in SQL statement?

Posted on 2008-06-10
5
1,095 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 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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 …

809 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