• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1406
  • Last Modified:

255 character limit in each cell - workaround

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
szadroga
Asked:
szadroga
  • 8
  • 5
  • 3
2 Solutions
 
TextReportCommented:
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
 
szadrogaAuthor Commented:
I am using Access 2003 and I am referencing this data within a section on a report I am developing.
0
 
TextReportCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
szadrogaAuthor Commented:
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
 
TextReportCommented:
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
 
TextReportCommented:
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
 
szadrogaAuthor Commented:
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
 
TextReportCommented:
"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
 
szadrogaAuthor Commented:
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
 
TextReportCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
szadrogaAuthor Commented:
I will give this a try.  Thanks for everyone's help.
0
 
TextReportCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
Yeah,
I answered a Q a while back where I actually had to "Split" a memo field into 255 character chunks!
;-)

Jeff
0
 
TextReportCommented:
Hi Jeff, ok but it is curious.
Best wishes, Andrew
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 8
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now