Solved

255 character limit in each cell - workaround

Posted on 2009-03-30
16
1,395 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access / SQL Server - ODBC Problems 16 59
Want my table columns to autofit. 7 63
Best way to get data into a database 12 56
Tabbed form question 5 20
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

739 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