Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: MS Access SQL - data type mismatch
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2006
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default MS Access SQL - data type mismatch

    Hi. While I've been writing Oracle SQL queries for a few years now, I'm relatively new to MS Access SQL. There are surprisingly few resources available online (via Google) for MS Access SQL. Given that, I'm having difficulty figuring out how to format a field which is text as a number (or vice versa). Do I use FORMAT()? Is it like this: FORMAT(FieldName,'General Number') ?

    Thank you.

  2. #2
    Board Regular
    Join Date
    Jan 2009
    Posts
    1,164
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access SQL - data type mismatch

    are you doing a select statement ?

    select * from my_table where my_table.FieldName = cdbl ( a_text_value )

  3. #3
    Board Regular
    Join Date
    Oct 2006
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access SQL - data type mismatch

    Yes, I'm using a SELECT statement.

    While I'm able to go into Design View and change the data type that way, it's kind of a hassle. I'd prefer to do this via SQL.

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,142
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: MS Access SQL - data type mismatch

    Can you show the SQL that's giving you a type mismatch?
    If posting code please use code tags.

  5. #5
    Board Regular
    Join Date
    Oct 2006
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access SQL - data type mismatch

    It may not be very helpful. It's literally: AND A.FieldName = B.FieldName. The issue is that Table A has the value expressed as numeric, whereas Table B has the same value formatted as text.

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,142
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: MS Access SQL - data type mismatch

    Is this criteria for a join?
    If posting code please use code tags.

  7. #7
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,467
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: MS Access SQL - data type mismatch

    format can work here, but it would always return text (so you would want to format the number and compare to the text value).

    Also in MSAccess the C functions (CInt, CDbl, CStr, etc.) will cast values to other datatypes, so probably good is to convert the text to number also (except would probably blow up if you try to convert a non-numeric text string to a number so beware).

    Code:
    where format(A.FieldName1,"#") = B.FieldName2
    or
    Code:
    where A.FieldName1 = CInt(B.FieldName2)
    Last edited by xenou; May 11th, 2018 at 02:29 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  8. #8
    Board Regular
    Join Date
    Oct 2006
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access SQL - data type mismatch

    Very helpful. Thank you.

  9. #9
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,467
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: MS Access SQL - data type mismatch

    Actually I should have suggested CLng - cast as long integer

    Code:
    where A.FieldName1 = CLng(B.FieldName2)
    MSAccess ints are what most other systems call short ints - limited to values between -32768 to 32767. Most Access number fields are Long Integers or Doubles.
    Last edited by xenou; May 11th, 2018 at 03:21 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  10. #10
    Board Regular
    Join Date
    Jan 2009
    Posts
    1,164
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access SQL - data type mismatch

    Quote Originally Posted by noisepoet View Post
    Yes, I'm using a SELECT statement.

    While I'm able to go into Design View and change the data type that way, it's kind of a hassle. I'd prefer to do this via SQL.
    that was sql

    its a simple selct statement

    select * from my_table where my_table.FieldName = cdbl ( a_text_value )

    are you sure you've done oracle for years ?
    Last edited by james_lankford; May 11th, 2018 at 03:32 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •