?
Solved

Data Type Mismatch in criteria expression!!!

Posted on 2003-03-16
4
Medium Priority
?
232 Views
Last Modified: 2010-08-05
hello,

   I have written a code in VB that should do a query in Microsoft Access
   and show the result in my labels in my forms.

   the code is here:

      Dim table_name as String
      Dim sql as String
      table_name = db.TableDefs(1).Name
      sql = "select * from wholestuff where codestuff=" & table_name
      Set rs = db.OpenRecordset(sql, dbOpenDynaset)
      Label1.Caption = rs("namestuff").value

   in which namestuff is the name of one of the col in wholestuff table
   and codestuff is the name of one of the col in table_name table that
   it paly the role of primary key in this table and it's type is Text.

    but in running this code I get this error:
        "Data Type Mismatch in criteria expression"
         
    why???

        with regards
0
Comment
Question by:marycore
[X]
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
4 Comments
 
LVL 2

Expert Comment

by:saurabhgupta
ID: 8149630
Use,
sql = "select * from wholestuff where codestuff='" & table_name & "'"

If table_name is a string you need 's around it.

Regards
Saurabh
0
 
LVL 11

Accepted Solution

by:
supunr earned 200 total points
ID: 8149649
Dim table_name as String
     Dim sql as String
     table_name = db.TableDefs(1).Name
     sql = "select * from wholestuff where codestuff=""" & table_name & """" ' <- need double quotes around string values
     Set rs = db.OpenRecordset(sql, dbOpenDynaset)
     Label1.Caption = "" & rs("namestuff").value

' "" & - in case rs("namestuff").value is null

Good Luck!
0
 

Expert Comment

by:CleanupPing
ID: 8532229
Hi marycore,
This old question (QID 20553015) needs to be finalized -- accept an answer, split points, or get a refund.  Please see http://www.cityofangels.com/Experts/Closing.htm for information and options.
0
 
LVL 6

Expert Comment

by:GPrentice00
ID: 8956484
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

-->Accept supunr's comments as answer

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

GPrentice00
EE Cleanup Volunteer
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month13 days, 10 hours left to enroll

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