Solved

255 character limit in each cell - workaround

Posted on 2009-03-30
16
1,390 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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