Access VB select statement

Posted on 2003-03-18
Medium Priority
Last Modified: 2007-12-19
I am trying to write a select statement in vb for access, but whenever I open the recordset it gives a data type mismatch error.

Dim rTestCells As Recordset
Dim strSQL2 As String
Dim iSolId As Variant

strSQL2 = "SELECT utcid FROM tbl_Temp_Import WHERE upcid = " & iSolId
Set rTestCells = dbs.OpenRecordset(strSQL2)

I'm running a loop from another recordset that populates the iSolId variable that works fine, but this select statement bombs out whenever the Where clause is added.

Any ideas?
Question by:JaiTrai
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
LVL 28

Accepted Solution

TextReport earned 80 total points
ID: 8162870
if the upcid field is text then use

strSQL2 = "SELECT utcid FROM tbl_Temp_Import WHERE upcid = " & Chr(34) & iSolId & Chr(34)

the Chr(34)'s put in " around your text.

Cheers, Andrew

Expert Comment

ID: 8163186
I will say like TestReport this is probably because this is a text field.  You can also use this synthaxe

"SELECT utcid FROM tbl_Temp_Import WHERE upcid = '" & iSolId & "'"

Author Comment

ID: 8169134
thanks for the information it works great now.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

801 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