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: 52
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: 69,081
Joined: 19-June 07
From: SunnySandyEggo


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,951
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: 52
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: 52
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: 1,036
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.

--------------------
All journeys have secret destinations of which the traveler is unaware ... Martin Buber
Go to the top of the page
 
Doug Steele
post Feb 16 2017, 04:20 PM
Post#7


UtterAccess VIP
Posts: 20,951
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: 52
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,951
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: 52
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,951
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
 
River59
post Feb 20 2017, 09:30 AM
Post#12



Posts: 1,036
Joined: 7-April 10
From: Detroit, MI


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

With:
ALTER TABLE 103H_TechnicalDescription_MakeTbl alter column Technical Description LONGTEXT;

Let us know if this works.
This post has been edited by River59: Feb 20 2017, 09:31 AM

--------------------
All journeys have secret destinations of which the traveler is unaware ... Martin Buber
Go to the top of the page
 
robleh7
post Feb 20 2017, 10:52 AM
Post#13



Posts: 52
Joined: 27-June 13



River59, first thx for helping consistently with this issue. And your tip does work partially!

First, let me say having a space between title name in the column was causing a read problem with the ms database engine. When I changed the name to TechnicalDescription that problem was solved.

And as you suggest the data type name LONGTEXT without any character length argument works. That is the SQL query runs. But, it still truncates at 255 characters. But, the table is NOT restricted to 255 characters. I can go to the field and add more text manually. This implies that the field is formatted to allow more than 255 characters but for some reason query truncates at 255.

I am so close now, I can taste it!
Go to the top of the page
 
River59
post Feb 20 2017, 11:19 AM
Post#14



Posts: 1,036
Joined: 7-April 10
From: Detroit, MI


I just found this on Google:

QUOTE
In the design view in the field properties for the text format for long text, simply select "Rich Text". All of the text then appears. It seem that as long as "Plain Text" is selected, then it is limited to 255 characters. When "Rich Text" is selected, that limit disappeared. answers.microsoft.com/en-us/office/forum/… – Marged Dec 15 '15 at 2:33


Not sure if I can post this link to the quote above but I'll try ... http://stackoverflow.com/questions/3427980...limiting-to-255

Not sure how you can change this with ALTER TABLE but it may point you to an answer.

--------------------
All journeys have secret destinations of which the traveler is unaware ... Martin Buber
Go to the top of the page
 
robleh7
post Feb 20 2017, 12:12 PM
Post#15



Posts: 52
Joined: 27-June 13



Actually, it is formatted as rich text. I think field is going to need to be converted. Some function that can convert this field is what I need. I have been searching for that now.

I did look at the page you posted. Saw this during my research, but it doesn't solve the problem also, Format under properties is empty no formatting characters like @, > etc there.

Strange, but this is becoming a problem that I think only VBA can handle.
Go to the top of the page
 
robleh7
post Feb 21 2017, 03:38 PM
Post#16



Posts: 52
Joined: 27-June 13



River59, I figured out how to get beyond the 255 character limit. The solution is so simple, here comes some hyperbole...a child could do it.

The text field called TechnicalDescription is an expression with several sub-tables concatenations with the string & ','+" "+. All I had to do was create another text field. Cut some of the sub-tables from the first field and paste them into a second text field TechnicalDescription2. Voila, the problem solved. I just split the descriptions into two side-by-side text boxes not elegant but for my purposes, adequate.


This of course doesn't solve the character limit problem. MS should really get some software engineer working on that.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    28th March 2017 - 06:35 PM