Solved

255 character limit in each cell - workaround

Posted on 2009-03-30
16
1,386 Views
Last Modified: 2013-11-29
I am working in Access and I created a query that combines a few columns of data into one (called Notes).  In some cases the data is being truncated due to the 255 character limit.  Is there anyway in the query to change the field to a memo field.  I assume it is a text field now.  This Notes field was not part of the original table linking from excel so I am not sure how I can change it to a memo field.
Notes: IIf([Issues Value]<>'',"ISSUES: " & [Issues Value],'') & Chr(13) & Chr(10) & IIf([Staffing Value]<>'',"STAFFING: " & [Staffing Value],'') & Chr(13) & Chr(10) & IIf([Schedule Value]<>'',"SCHEDULE: " & [Schedule Value],'') & Chr(13) & Chr(10) & IIf([Scope Value]<>'',"SCOPE: " & [Scope Value],'') & Chr(13) & Chr(10) & IIf([Financials Value]<>'',"FINANCIALS: " & [Financials Value],'') & Chr(13) & Chr(10) & IIf([Risks Value]<>'',"RISKS: " & [Risks Value],'')

Open in new window

0
Comment
Question by:szadroga
  • 8
  • 5
  • 3
16 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 24021342
Tested this OK in Access 2007 with all your fields with 255 chars in each and it gives a total legnth of 1594 for the NOTES field in the query.
Tested Nulls and they did not cause a problem either but I would include the Chr(13) & Chr(10) inside the IIF though, unless of course you want the blank lines.
Can you please confirm Access Version and what you are doing with the results of the data.

Cheers, Andrew
SELECT IIf([Issues Value]<>'',"ISSUES: " & [Issues Value] & Chr(13) & Chr(10),'') & 

       IIf([Staffing Value]<>'',"STAFFING: " & [Staffing Value] & Chr(13) & Chr(10),'') & 

       IIf([Schedule Value]<>'',"SCHEDULE: " & [Schedule Value] & Chr(13) & Chr(10),'') & 

       IIf([Scope Value]<>'',"SCOPE: " & [Scope Value] & Chr(13) & Chr(10),'') & 

       IIf([Financials Value]<>'',"FINANCIALS: " & [Financials Value] & Chr(13) & Chr(10),'') & 

       IIf([Risks Value]<>'',"RISKS: " & [Risks Value] & Chr(13) & Chr(10),'') AS Notes

    , Len([Notes]) AS Expr1

FROM tblMemoData;

Open in new window

0
 

Author Comment

by:szadroga
ID: 24021454
I am using Access 2003 and I am referencing this data within a section on a report I am developing.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24021487
Can you test the legnth as I have in my example. Also in the report have you set the CanGrow property of the textbox and the section the NOTES are in to Yes?
Cheers, Andrew
0
 

Author Comment

by:szadroga
ID: 24021585
I made the change for the chr13 and ch10, but my data is still being truncated.  I will attach a screenshot of the data.

ISSUES: project not funded, staffed, scoped
STAFFING: project is not fully funded, so no staffing ahs been completed
SCHEDULE: Project schedule is unknown
SCOPE: scope has not been defined
FINANCIALS: Project is not funded
RISKS: Scope not defined, p

See how the RISKS data in truncated...


Sample-Screenshot.jpg
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24021600
I have tested this in Access 2002 (sorry I don't have 2003) and had exactly the same results. Therefore the questions really

1. Does the LEN() work in the query
2. "In some cases the data is being truncated due to the 255 character limit." do you mean all cases when there are more than 255 characters they get truncated or is it only certain records. If it is certain records then is it because some fields are null / empty strings?
3. As suggested earlier if the query is OK then check the CanGrow property.

Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24021654
Just tested your data in Access 2002 with my query putting some extra characters where the truncated data would be and it was fine with a length of 268.
Can you please create a new query that just concatenates your NOTES and returns the LEN also as see if you can get the full data.
Cheers, Andrew
ISSUES: project not funded, staffed, scoped

STAFFING: project is not fully funded, so no staffing ahs been completed

SCHEDULE: Project schedule is unknown

SCOPE: scope has not been defined

FINANCIALS: Project is not funded

RISKS: Scope not defined, pasahsjahsjh

Open in new window

0
 

Author Comment

by:szadroga
ID: 24021709
Is it being truncated on all occasions.  I could not get the Len() function to work, how would that fix my problem?  Doesn't that function just return the value of the string length?  I do have the CanGrow property for the textbox set to Yes on the report.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24021818
"Is it being truncated on all occasions." Is this within the query rather than the report?
Can you put up a sample file of the problem and I will take a look.
Cheers, Andrew
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:szadroga
ID: 24022035
Well its happening within the query and then when I set the control source for the textbox on the report to point to the Notes field, the data is being truncated on the report too.

I posted two screen shots of the data above this comment.  Is there something else you would like me to post?
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 100 total points
ID: 24022088
A sample database file containing a table and query that demonstrates the problem, I have tried to recreate this for myselft in both Access 2002 and 2007 but it is working for me.
Cheers, Andrew
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 400 total points
ID: 24022422
szadroga,

As an alternative, instead of trying to combine the data into one field and exceed the limit, why not simply only combine the data at the point at which it is to be displayed?
Using a textbox in the Form or in the Report.
(Texboxes have the same Character limit as memo fields , approx 65,000 characters.)

This works fine for me.

Here is a sample

JeffCoachman
Access-EEQ-24278071ConcatenateVa.mdb
0
 

Author Comment

by:szadroga
ID: 24022625
I will give this a try.  Thanks for everyone's help.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24028502
I still don't see why the query  fails in Access 2003 whereas it works in both Access 2002 and 2007.
Best Wishes, Andrew
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24028770
Andrew,
  "I still don't see why the query  fails in Access 2003 whereas it works in both Access 2002 and 2007."

I know there was a lot going on with this issue regarding Service Packs, Hotfixes, ...ect.

So perhaps it hase something to do with the Service Packs/Hotfixes installed on the particular machine?

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24028786
Yeah,
I answered a Q a while back where I actually had to "Split" a memo field into 255 character chunks!
;-)

Jeff
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24028832
Hi Jeff, ok but it is curious.
Best wishes, Andrew
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now