Memo field truncated in table but complete in form

[dbar10]

I am confused. I was having a problem with a report not showing the complete field based on a memo field in a query. I checked the query and the data was truncated. Then I checked the query that is the source for the form where the data is entered and it was truncated. I then looked at the table that the source query is based on and the Memo field was truncated. But the main form still has all the data in the memo field. Where is it getting it from? The table doesn't show it nor the query. Can you help?

 

[dhookom]

I expect you have some value entered into the Format property on the table or query or control. Also, a query will truncate a memo field to 255 characters if it is a totals query or contains "DISTINCT" or similar.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Thanks DHookum for your response. I have checked all those things. I don't see a problem. The [POC Table] is the source for my query and form. the field is [OrderDisc] it is set at Memo. When I look at the table the data is truncated to 255 characters. But, in my form [POC Form](Where the data is entered) all the data is there, even after I close down and come back. I don't understand how that can be. When I query the table to show the text in my report the data is as it appears in the table. Am I losing my mind? Also, something interesting, In my query for the report [POC 485 Query] I have a Len statement to count characters and it is counting all of the characters that should be in the field.

 

[dbar10]

I am sending the SQL for the {POC 485 Query]. This is what my main form is built on.

SELECT [POC Table].POCID, [POC Table].ClientID, [POC Table].[Client Full Name], [POC Table].ProgramID, [POC Table].OrderID, [POC Table].[HIClaim#], [POC Table].SOCDate, [POC Table].CertPeriodFrom, [POC Table].CertPeriodTo, [POC Table].[Medical Record#], [Provider# Table].[Provider #], [POC Table].ProvID, [Provider# Table].Program, [Client Table].Address1, [Client Table].Address2, [POC Table].CityID, City.City & ", " & State.State & " " & [Zip] AS CityST, City.City, State.State, [Client Table].Zip, [POC Table].StateID, [POC Table].CompanyID, [Company Setup].CoName, [Company Setup].CoAddr, [Company Setup].CoAddr2, [Company Setup].CoCity, [Company Setup].CoState, [Company Setup].CoZip, [Company Setup].CoCity & ", " & [Company Setup].CoState & " " & [Company Setup].CoZip AS CoCityST, [POC Table].DOB, [POC Table].Gender, [POC Table].PrimaryDiagID, [ICD9PrimDiag Query].[ICD-9], [ICD9PrimDiag Query].[Abbrev Description], [POC Table].PrimDiagDate, Len([SafetyMeas]) AS SafetyCount, [POC Table].SafetyMeas, Len([NutrtionReq]) AS NutriCount, [POC Table].NutrtionReq, [POC Table].OrdersDisc, Left([OrdersDisc],2600) AS Orders845, Len([OrdersDisc]) AS OrdersCount, IIf([OrdersCount]<=2600,"",Right([OrdersDisc],[OrdersCount]-2600)) AS Orders847, [POC Table].Goals, Len([Goals]) AS GoalsCount, [POC Table].NurseSign, [POC Table].AgencyReceived, [POC Table].Suppress, [POC Table].[26], [POC Table].AttendingSigndate, [POC Table].[28]
FROM [ICD9PrimDiag Query] INNER JOIN ([Provider# Table] INNER JOIN ((State INNER JOIN (City INNER JOIN ([Program Query] INNER JOIN (Gender INNER JOIN ([Company Setup] INNER JOIN [POC Table] ON [Company Setup].CompanyID = [POC Table].CompanyID) ON Gender.GenderID = [POC Table].Gender) ON [Program Query].ProgramID = [POC Table].ProgramID) ON City.CityID = [POC Table].CityID) ON State.StateID = [POC Table].StateID) INNER JOIN [Client Table] ON ([POC Table].ClientID = [Client Table].ClientID) AND (Gender.GenderID = [Client Table].Gender) AND (State.StateID = [Client Table].State) AND (City.CityID = [Client Table].City)) ON [Provider# Table].ProvNoID = [POC Table].ProvID) ON [ICD9PrimDiag Query].ICD9ID = [POC Table].PrimaryDiagID
WHERE ((([POC Table].ClientID)=[Forms]![Clients Add/Edit]![ClientID]));

Hope this helps. thanks

 

[dbar10]

Well, I have been doing some experimenting and found that is I put a subreport based on a query that just pulss the PK and the memo field desired, I get the desired results. It appears that there is just to much going on in the main query and by simplifying another query it takes care of the problem. I also had to put a subform with the same simplified query to enter the data and that caused all teh data to show in the table. Seems like a lot to work around but it works. Do you think this could cause me problems later?

 

[dhookom]

I expect the OrderDisc if bound to a control in a (report or group) (header or footer) section will get truncated.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya dbar10 . . .

In the table ... [blue]increase your record height[/blue] to tell if turnication applies. If its in the form its gotta be in the table. Perform the same for query.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[pattyjean]

I always have this issue - two things I learned.

Make sure you don't have the @ sign in the format box in the table memo field

or redo the query. Sometimes that works for me!! I don't know why but it has. Try it making a new query using the memo field to see if all the data is there.

But if someone knows a real solution please let us know

 

[dbar10]

pattyjean, yes I discovered that the data was there. it turned out that I had some faulty relationships in the source tables taht was causing the problems. I have learned to always go back and check the relationships and make sure they are efficient. Thanks