How to change data type to Short text in Access

Eethornto2346New MemberJoinedApr 22, 2014Messages7Jul 20, 2015#1Here is my dilemma:I'm pulling data from SQL into access and trying to create a query to link info in 2 tables toget

How to change data type to Short text in Access

E

ethornto2346

New MemberJoinedApr 22, 2014Messages7
  • Jul 20, 2015
  • #1Here is my dilemma:

    I'm pulling data from SQL into access and trying to create a query to link info in 2 tables together. Unfortunately, the data that I need to link are saved in 2 different formats in SQL tables (1 is Short Text the other Long Test). I do not have access to change the formats in SQL tables (they really should be a number field but that's another issue..) so I'm trying to find a work around. I was thinking I could create a query and change the data type for one and then join them in a 2nd query but have not had any luck. I've been using a new column and trying "FieldAs---" to get the desired results but either it only pulls some data, which is strange, or gives me an error.

    Long story short, is there a simple way that I can do this in a query? Or is there another way I can achieve the outcome I need?
Joe4

Joe4

MrExcel MVP, Junior AdminJoinedAug 1, 2002Messages64,700Office Version
  1. 365Platform
  2. Windows
  • Jul 20, 2015
  • #2
SQL tables (1 is Short Text the other Long Test)Click to expand...I have worked with both Access and SQL for years, and I have never heard of any data types called "Short Text" or "Long Test".

Here are a list of SQL data types: SQL General Data Types
Here are a list of Access data types: SQL Data Types for MS Access, MySQL, and SQL Server

If you view the table in Design View in Access, what exactly does it show for the data types of these two fields?E

ethornto2346

New MemberJoinedApr 22, 2014Messages7
  • Jul 20, 2015
  • #3When I open the linked tables and go to Design view, I get a list of my fields, "Field Names" in the first column and in the 2nd column I get Data Type and a good majority of them are either Short Text, Long Text or Date/Time. Not sure if we are talking about the same thing but thought these were pretty normal.

    Sorry, looking at design view in Access if that matters. They are coming through as that data type....if that helpsLast edited: Jul 20, 2015
Joe4

Joe4

MrExcel MVP, Junior AdminJoinedAug 1, 2002Messages64,700Office Version
  1. 365Platform
  2. Windows
  • Jul 20, 2015
  • #4OK, that looks like a new change in Access 2013 (https://support.office.com/en-US/ar...ess-2013-BC006FC3-5B48-499E-8C7D-9A2DFEF68E2F). I am using Access 2007, and it looks like "Short Text" translates to "Text" and "Long Text" translates to "Memo.

    "Short Text" fields only allow 255 characters. So try this in Access.
    - Create a query on the table with the "Long Text" field, and enter this calculated field on this "Long Text" field:
    Code:Left([LongTextFieldName],255) as NewFieldName
    Then, join this query to the other table, joining on this Calculatd Field Name to the Short Text field in your other table.E

ethornto2346

New MemberJoinedApr 22, 2014Messages7
  • Jul 20, 2015
  • #5You're the man! That did the trick! So just so I understand, all we did there was take the long text field and pulled in just the max for the short text, which in turn basically converted it to "Short Text"?
Joe4

Joe4

MrExcel MVP, Junior AdminJoinedAug 1, 2002Messages64,700Office Version
  1. 365Platform
  2. Windows
  • Jul 20, 2015
  • #6Yep, that's what we did, more or less.

Video liên quan