UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Changing To Long Text Does Not Solve A Truncation Problem, Access 2013    
 
   
robleh7
post Feb 16 2017, 02:07 PM
Post#1



Posts: 41
Joined: 27-June 13



I am using Access 2013 at work. I have what seems to be an insoluble problem that has arisen during course of my querying a dB I've created.

I've been making queries that use a linked Xcel sheet with several columns. I have a tables for each column on the xcel sheet. I have left joins from the sheet to these tables to extract a string of text. The extraction is done in the expression builder interface. it simply takes the left joined tables text and concatenates them into one long text expression. Here is where the problem occurs. The field where the expression is evaluated is by default a short text field. Character limit 255. However, I tried to circumvent this by turning the query into a make-table. I then took the output table and reformatted that field as long text. But, the 255 limit still occurs. I then formatted it as RTF, but still the 255 limit applies. I am shocked at this behavior in Access. Every site I visit on the Web points to these changes as the solution, but not so here. Worst yet, when I make the text format for each linked table rich text the html mark-up tags actually show in the field with the description text!

Due to proprietary restrictions I can't show any of my query results or even the expression builder that generates it.

Plz note this is Access 2013, so the old memo field type is gone. I don't believe running this in SQL view would change anything.

Any thoughts, suggests are welcome.

Robleh
This post has been edited by robleh7: Feb 16 2017, 02:09 PM
Go to the top of the page
 
theDBguy
post Feb 16 2017, 02:11 PM
Post#2


Access Wiki and Forums Moderator
Posts: 68,695
Joined: 19-June 07
From: SoCal, USA


Hi,

Have you tried doing it with code rather than pure SQL? Just curious...

--------------------
Go to the top of the page
 
Doug Steele
post Feb 16 2017, 02:14 PM
Post#3


UtterAccess VIP
Posts: 20,831
Joined: 8-January 07
From: St. Catharines, ON (Canada)


What's the SQL of the query you're trying to build?

--------------------
Go to the top of the page
 
robleh7
post Feb 16 2017, 03:22 PM
Post#4



Posts: 41
Joined: 27-June 13



You mean in VBA? No, I haven't. I didn't think this rose to the level of writing a sub-routine.
I guess this would be a case of using the CONVERT function. Db guy, I've seen your excellent work for other posters. Thank you for suggesting this.
Go to the top of the page
 
robleh7
post Feb 16 2017, 03:31 PM
Post#5



Posts: 41
Joined: 27-June 13



I'm not doing it in SQL. Simply in the GUI interface expression builder. As I said switching to SQL view in Access wouldn't change the problem. Access is failing to allow more than 255 characters for the data type long integer. Should I approach MS directly maybe?
Go to the top of the page
 
River59
post Feb 16 2017, 03:42 PM
Post#6



Posts: 959
Joined: 7-April 10
From: Detroit, MI


CODE
Access is failing to allow more than 255 characters for the data type long integer


I'll just assume this is a typo ... beyond that, your posts just aren't clear about what you are doing and where the problem is. Is the field on the table formatted as long text?

CODE
The field where the expression is evaluated is by default a short text field


Without seeing what you are attempting to do, I wonder if anyone is going to be of much help.

--------------------
Just don't drink the kool aid ...
Go to the top of the page
 
Doug Steele
post Feb 16 2017, 04:20 PM
Post#7


UtterAccess VIP
Posts: 20,831
Joined: 8-January 07
From: St. Catharines, ON (Canada)


QUOTE
I'm not doing it in SQL. Simply in the GUI interface expression builder.

If you're building a query, you're working with SQL. Access builds the SQL for you. Change to the SQL view to see what it's built for you.

--------------------
Go to the top of the page
 
robleh7
post Feb 17 2017, 08:45 AM
Post#8



Posts: 41
Joined: 27-June 13



River59, you've got a point there.

So, here is an example of how I've attempted to fix the data type problem.

I wrote a simple SQL alter table query that attempts to change the data type of the field Technical Description on a table that has been created by a make table query. I've used the data type names TEXT, LONGTEXT and LONG TEXT, all of which the MS Access database engine fails to recognize. below is an instance of using it with 255 as the character length:

alter table 103H_TechnicalDescription_MakeTbl alter column Technical Description text (255)

which throws the error 3293 syntax error in ALTER TABLE statement this error persists when I change the data type names I've listed above and reducing the number of characters doesn't help either. But, wait there is more.

I took another field on this table that is formatted as SHORT TEXT with character length 255, popped it into the same SQL statemen, changed the character length to 50 and it worked with no problem whatsoever!
This post has been edited by robleh7: Feb 17 2017, 08:49 AM
Go to the top of the page
 
Doug Steele
post Feb 17 2017, 01:04 PM
Post#9


UtterAccess VIP
Posts: 20,831
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Try Memo, rather than Text (255). (Text (255) is still officially Short Text)

--------------------
Go to the top of the page
 
robleh7
post Feb 17 2017, 01:11 PM
Post#10



Posts: 41
Joined: 27-June 13



It's my understanding that long text replaced memo starting with access 2013 or perhaps even 2010.

Thx for comment however.
Go to the top of the page
 
Doug Steele
post Feb 17 2017, 05:23 PM
Post#11


UtterAccess VIP
Posts: 20,831
Joined: 8-January 07
From: St. Catharines, ON (Canada)


They've chosen to present it as "Short Text" and "Long Text" in the Properties window. The data type is still CHAR and MEMO respectively.

It's just like how they present Left, Right, Height, Width etc. as inches or centimeters in the Properties window, but they're still stored as Twips.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th February 2017 - 04:01 AM