Results 1 to 6 of 6
  1. #1
    Tomfernandez1 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Feb 2011
    Posts
    74

    Data type mismatch error

    The database that applies the following code has had a recent change:



    strSQL = "SELECT * FROM [KT VANPOOL DRIVER RECORDS] WHERE [ACTION] = " & Forms![KITSAP TRANSIT DRIVERS].[ACTION]

    The change was formatting the [ACTION] field from an number to a text format. Doing so would allow an alphanumeric input in this field on a Form.

    This change has resulted in the following error:

    "Run-time error '3464': Data type mismatch in criteria expression."

    Any help to resolve this problem is much appreciated!!

    Tommy

  2. #2
    drexasaurus's Avatar
    drexasaurus is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Santa Ana, CA
    Posts
    60
    You have to use quotes around text values in SQL statements, if ACTION is a text field.

    Something like this:
    "SELECT * FROM [KT VANPOOL DRIVER RECORDS] WHERE [ACTION] = """ & Forms![KITSAP TRANSIT DRIVERS].[ACTION] & """"

    Double quotes makes one quote in a string (so you can insert a quote character in a string without ending the string literal)

    Afterwards, the content strSQL would look like this if Action was abc1:
    SELECT * FROM [KT VANPOOL DRIVER RECORDS] WHERE [ACTION] = "abc1"

  3. #3
    Tomfernandez1 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Feb 2011
    Posts
    74

    RE: Data type mismatch error

    That was fast! Problem solved.

    Thanks for the help!!

    Tommy

  4. #4
    drexasaurus's Avatar
    drexasaurus is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Santa Ana, CA
    Posts
    60
    Date values have to be in between # characters too, just for future reference, like: #1/27/2007#

  5. #5
    Tomfernandez1 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Feb 2011
    Posts
    74

    RE: Data type mismatch error

    Many thanks for sharing your knowledge!!

    Tommy

  6. #6
    JayGee1969 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    17
    I have a similar problem - I have a form that was working fine until the customer asked me to change the data type in a Primary Key field from a number to text and now I get the dreaded Data type mismatch error.

    The bit of code I am having problems with is: stLinkCriteria = "[OrderID]=" Me![OrderID] where OrderID is the field that is now text instead of number. I have tried adding quotation marks in but cannot get this to work and wondered if you could advise me exactly how it should read to make it do as I want.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error#13 Type Mismatch
    By Nistin27 in forum Access
    Replies: 8
    Last Post: 08-17-2011, 04:15 PM
  2. type mismatch error?
    By hlcarlin in forum Programming
    Replies: 1
    Last Post: 07-07-2011, 08:30 AM
  3. Data type mismatch error
    By AccessUser123 in forum Queries
    Replies: 1
    Last Post: 05-22-2011, 07:48 PM
  4. Replies: 2
    Last Post: 05-17-2011, 02:40 PM
  5. Data type mismatch error on all of my queries!
    By MarkGLyons in forum Queries
    Replies: 3
    Last Post: 12-27-2010, 01:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums