Changing Data type in query

[ethornto2346]

Here 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]

SQL tables (1 is Short Text the other Long Test)

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?

 

[ethornto2346]

When 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 helps

 

[Joe4]

OK, 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:

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.

 

[ethornto2346]

You'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]

Yep, that's what we did, more or less.

 

[ethornto2346]

Awesome.

Thanks again.