Exhausted all of my options and STILL cannot get more than 255 char in report!!!

ss6857

Registered User.
Local time
Today, 13:27
Joined
Jul 12, 2011
Messages
38
I have a Master Table that is linked in from SQL. The Datatype of the column I am having trouble with in SQL is Text. I link the table into Access and when I look up the datatype of the troublesome column in Access, it is Memo (some fields go up to almost 800 char). I then make a query using this table and when I run the query, the "Memo" field shows all of the characters. Then when I build a report based on this query, it cuts it down to 255!!! I have looked on a lot of forums and here are things I remember I have done and double checked and it still doesn't work:

Unicode compression set to Yes
No format (anywhere!!)
No *, DISTINCT, UNION or similar in the query
Use First, not Group By
I have tried expressions like Left([MemoField], 800) in the query and report
Unique values set to no
No crosstab query or summary query (if other fields have group by, is it automatically a summary query?)
Grow/Shrink set to yes

Does anybody have any other bits of knowledge? Thank you for any and all help.
 
Is there any criteria whatsoever for this report? If so, how is it set up?
 
Well, it is a customer table, so when I make the query, I make the customer ID number equal the customer I want to create a report for. I also use expressions like: Level1: Sum(IIf([dbo_qry_CustomerTable]![Level]=1,[dbo_qry_CustomerTable]![Price],Null)) Other wise, I don't use any other tables or anything.
 
If there is ANY, and I repeat - ANY criteria on the query that limits the records then you have to do it this way:

1. Create a query WITHOUT the memo field and put your criteria on it.

2. Save that query and then use it in ANOTHER query which has that first query and then the original table also with the links set to the key field/fields. Add all fields from the first query into the second one as the selected fields and then add ONLY the memo field from the table into the selected fields.

Only if you do that will it work correctly.
 
Thank you proving me wrong, I did have options left! I have not tried that yet, and thank you for putting it in terms that even I could understand. I did what you said and it still did not work.. Maybe because it was my first time doing that, I did it wrong. Here is what my SQL part of it looks like:

SG is my first query
MasterTable is the original table

SELECT SG.Name, SG.Category_ID, SG.Category, SG.Product, SG.[Level1], SG.[Level2], SG.Note_ID, First(MasterTable.Note_Desc) AS FirstOfNote_Desc
FROM MasterTable INNER JOIN SG ON MasterTable.Note_ID = SG.Note_ID
GROUP BY SG.Name, SG.Category_ID, SG.Category, SG.Product, SG.[Level1], SG.[Level2], SG.Note_ID;
 
I didn't mention that you have to include the GROUPINGS on the first query too. You can't group in the query with the memo field or else it will truncate also.
 
Success!!! Big Thanks!!!

Glad we could help out.
thumbsupsmile.jpg
 

Users who are viewing this thread

Back
Top Bottom