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
Solved

Linking a memo datatype to a textbox in a report truncates data at 255.

Posted on 2004-04-01
16
503 Views
Last Modified: 2011-09-20
I have tried to apply the solution from " Solution Title: Report Text Box - HURRY!" but it doesn't work for me, for some reason.  Tha data still truncates at 255 characters.  Please try to keep your answer simple.  I don't know VBA.
Mafunzalo
0
Comment
Question by:Mafunzalo
  • 10
  • 3
  • 2
  • +1
16 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 10737074
I just used the report wizard to build a report based on a memo and it worked fine with a text box

Try doing a "dummy" rpt then copy and paste the textbox into your real rpt
Works fine for me!

Good Luck!

Dave
0
 
LVL 34

Expert Comment

by:flavo
ID: 10737077
>> Try doing a "dummy" rpt

Using the rpt wizzard
0
 

Author Comment

by:Mafunzalo
ID: 10737214
I tried using the report wizard and design view.  neither worked.
In design view, the fiels in question was the only field in the report.
In both attempts, Can Grow and Can Shrink were Yes.
I must be missing something.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:Mafunzalo
ID: 10737250
BTW, I didn't get the dummy reports to work, so I didn't copy&paste into the real report...
0
 
LVL 39

Expert Comment

by:stevbe
ID: 10737331
delete the control from the report, select field list from the view menu and drag the memo field name onto the report. Access can get goofed up if you add an empty textbox and then change the datasource to memo or if you copy an existing textbox bound toa text field and then change the control source to a memo field.

Steve
0
 

Author Comment

by:Mafunzalo
ID: 10737404
I created a report using Design View (totally empty)
I clicked Record Souce in the Properties box to get the query I'm interested in.  The Field List populated.
I dragged and dropped the memo field from the Field List,
Went back to Properties and selected Yes for "Can Grow" and "Can Shirnk"
Hit View and the fields are still limited to 255.
0
 

Author Comment

by:Mafunzalo
ID: 10738666
Whoa.  New information.
   The datatype of the field of the table that the query is working on is Memo and has more than 255 characters.  OK.
   When I run the report that includes the query, the data is limited to 255 characters.
   When I run the query alone, the character-length of the data that is pulled from the table is limited to 255 characters.  The table that is created from the query has the limitation, not the report.  NOW what?
0
 

Author Comment

by:Mafunzalo
ID: 10740677
Another update that supoports the "Whoa." update.
When I do a report wizard and NOT use the query, the fields are not truncated!
But I have some relationships in the query that I need to keep (and don't know how to create in the Report Wizard, if it's possible).  How can I reference the query in the report but keep the complete memo fields printing?
0
 

Author Comment

by:Mafunzalo
ID: 10742727
I increased the points to 300.  I gotta get an answer!  
0
 
LVL 5

Accepted Solution

by:
burtdav earned 300 total points
ID: 10838355
You may be able to make another query that joins the results of your query (in which the memo field is truncated) with the full memo field straight from the table.

Something like this:
SELECT q.*, t.MyMemoField AS MyMemoField_Full
FROM MyQuery AS q, MyTable AS t
WHERE q.MyTable_ID = t.ID

If you post your query, I can probably write this second query if you need me to
0
 

Author Comment

by:Mafunzalo
ID: 10839419
Thanks.  I'll give it a try tomorrow.
0
 

Author Comment

by:Mafunzalo
ID: 10847011
Computers are really strange.
My query is fairly complex and in order to try your solution, I started removing tables I could live without. I wpuld remove a table and then test it. Still bad.  Remove. Test. Bad. Until there was just the main table where the majority of the data lived. Then I got the error, "The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data." [OK] [Help]  OK closes it, Helpbrings up a blank screen.  This is a select query. I removed the remaining fields until there was one: the memo field.  I still get the "field is too small" error. Compact and repair twice.  Same error.

I opened the SQL view,
SELECT DISTINCT DataTable.Q_230200
FROM DataTable;

I removed the DISTINCT, and it works.  I went back to the original q
0
 

Author Comment

by:Mafunzalo
ID: 10847012
Computers are really strange.
My query is fairly complex and in order to try your solution, I started removing tables I could live without. I wpuld remove a table and then test it. Still bad.  Remove. Test. Bad. Until there was just the main table where the majority of the data lived. Then I got the error, "The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data." [OK] [Help]  OK closes it, Helpbrings up a blank screen.  This is a select query. I removed the remaining fields until there was one: the memo field.  I still get the "field is too small" error. Compact and repair twice.  Same error.

I opened the SQL view,
SELECT DISTINCT DataTable.Q_230200
FROM DataTable;

I removed the DISTINCT, and it works.  I went back to the original q
0
 

Author Comment

by:Mafunzalo
ID: 10847066
...continued...
I went back to the original query, removed the DISTINCT, and it works!  There are duplicates, however.

I started with a clean query, put in
SELECT DataTable.Q_230200
FROM DataTable;

And it worked.  I added the DISTINCT and it failed with the "field is too small" error.

I will work to get the duplicates out without using DISTINCT.  Does anyone know why DISTINCT caused the fields to be shortened?
0
 
LVL 5

Expert Comment

by:burtdav
ID: 10864770
DISTINCT does about the same thing as a GROUP BY clause including all selected fields. I know (I recently had this problem) that if you GROUP BY a Memo type field, it gets truncated to 255 characters - it makes sense that Access won't group by an arbitrary length field. DISTINCT is probably doing the same thing.
0
 
LVL 5

Expert Comment

by:burtdav
ID: 10864776
If your original query was DISTINCT, this may make the re-joining solution I gave unnecessary.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

808 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