Memo field cut off on report

[ed333]

Hello all,

I have discovered a very disturbing problem in my project. Some of the memo fields are being truncated when my report is being generated, even though I have CanGrow set to Yes for this particular textbox. The data is retained in the table, and displays perfectly on the form, but I must have it also showing in the report. The memo field in question is for a description of time being billed, so it must be visible to our clients on the bills (the report I'm having problems with).

Thanks so much for any help,

Ed

 

[ed333]

Additional information

I have discovered that the field is being truncated due to a query:

SELECT DISTINCT tblCharges.CHGATTY, tblCharges.RESPATTY, tblCharges.CLNTNO, tblCharges.FILENO, tblCharges.DATE, tblCharges.RecordNumber, tblCharges.CHARGE, tblCharges.UNITS, tblCharges.RATE, tblCharges.DISB, tblCharges.DESCMEMO, tblCharges.DESC1, tblCharges.HOLDFLAG, tblCharges.BILLFLAG, tblCharges.WRITEOFF, tblFILES.TITLEFULL, tblClients.CLNAME, tblClients.STREET, tblClients.CITYSZ, tblClients.TELEA, tblClients.TELLB
FROM tblFILES INNER JOIN (tblClients INNER JOIN (tblCharges LEFT JOIN tblAttorneyNumbers ON tblCharges.CHGATTY = tblAttorneyNumbers.Initials) ON tblClients.CLNTNO = tblCharges.CLNTNO) ON tblFILES.FILENO = tblCharges.FILENO
WHERE (((tblCharges.RESPATTY)=[Forms]![Form3].[Combo0]) AND ((tblCharges.DATE) Between [Forms]![Switchboard]![First] And [Forms]![Switchboard]![Last]) AND ((tblCharges.BILLFLAG) Is Null) AND ((tblCharges.WRITEOFF) Is Null))
ORDER BY tblCharges.RESPATTY, tblCharges.CLNTNO, tblCharges.FILENO, tblCharges.DATE, tblCharges.RecordNumber;

Does anyone know what could be causing the field DESCMEMO to be truncated to 255 characters?

Thanks

 

[M8KWR]

can you post the db?

 

[ed333]

It's too big to post, and the data is sensitive client data for my lawfirm. I can post the structure with no data, though. I'll be at the office later this afternoon (Saturday 11/13) and attempt to do so.

Thanks
Ed

 

[DrGoneAstray]

Solution to trunctating memo field

MS Access does not allow sorting on memo fields. When is processes a SQL DISTINCT request, it must perform a search, so it truncates the memo field to a Text field (i.e., VARCHAR(255)) so it can sort it for the DISTINCT call. Unfortunately, you lose the rest of your field. You would end up with the same truncation if you tried to invoke a GROUP BY on the memo field.

So, here are a couple of solutions you can try (they work):

SELECT s.*
FROM Stock s
WHERE EXISTS (SELECT DISTINCT SS.StockPK, SS.StockName, SS.StockPrice FROM Stock SS where SS.stockPK = s.StockPK)
ORDER BY s.StockName

What that does is put the DISTINCT keyword in the subquery, so the field returned from the main query is NOT under the truncation constraint. The only weakness here is that if you include too many fields in the subquery, or put a MEMO field there, the Access driver will throw a memory error.

Second way:
Select * from Stock

Then, if your application environment supports requerying (I use Cold Fusion), then you can do the SELECT DISTINCT in the requery (since that probably uses an internal SQL engine) and return the results of the requery.

You can also try a pseudo-requery using an Array sort, or a struct search.

Good luck!

 

[ed333]

Removing the word "Distinct" from the sql worked. I guess I didn't really need it anyway, since I have my autonumbered primary key being selected as one of the fields.

Thanks!

Ed

 

[ncarty97]

Hope you don't mind me jumping in on this

I am having a similar truncation problem, hope you can help:

I'm a bit baffled on this:

I have a database that has 6 fields in it that I want to pull into excel via a VBA macro based on the account number the user puts into a form.

It works perfect, except one thing. The last field, that houses the notes previous users have entered for the account, truncates when I pull it into Excel. Here is the code I am using:

Dim Db As DAO.Database

Dim rst As DAO.Recordset
Dim mPathDAL As String
Dim mTable As String
Dim strSQL As String

Dim mCount As Integer
frm_AcctNbr.Show
mPathDAL = "\\crpdalgrp01\portview\CaseNotesDAL.mdb"


mTable = "[CaseNotes]"

strSQL = "SELECT CaseNotes.ACCT_7, CaseNotes.CREATE_TMSTMP, CaseNotes.CATEGORY_DESC, CaseNotes.PACE_NB_ID, CaseNotes.FULL_NAME, CaseNotes.COMNT_TXT " & _
" FROM CaseNotes" & _
" WHERE (((CaseNotes.ACCT_7) = '" & myAccount & "'))" & _
" ORDER BY CaseNotes.CREATE_TMSTMP DESC;"

Set Db = Engine.Workspaces(0).OpenDatabase(mPathDAL)

Set rst = Nothing

Set rst = Db.OpenRecordset(strSQL)


Workbooks.Add
Range("B3").CopyFromRecordset rst


rst.Close

Db.Close
End If

It pulls everything but the last field (COMNT_TXT) correctly. For that field, I only get the first letter of the data.

To check, I did a couple of things. In Access, I created a query to pull the data for one account. The COMNT_TXT data came out fine. In Excel, I inserted a query into a spreadsheet, again it pulled the COMNT_TXT data correctly.

I stepped through the code, and when the recordset (rst) was pulled, I looked down in the locals window. Under Item 6 of the rst fields, it shows the value as I would expect, with the full text of the data.

Only other thing I can think of is that this is the only field in the table that is a Memo type.

It seems that their must be something wrong with my code since it will pull into excel by inserting a query into a sheet.

Thanks!

Nate

 

[Shawna]

I am quoting Allen Browne who answered my question:

"The memo will be truncated if you use a source query that involves any
aggregation (e.g. a GROUP BY clause or DISTINCT predicate in a SQL
statement), or formatting (e.g. UCase(), or something in the Format property
of the memo field in the table or the Format property of the text box on a
form).

In a Totals query, you may be able to avoid the issue by using First(MyMemo)
instead of grouping by the memo field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org."