Big problem with ms-access MEMO field (text truncated and ends with strange āई char)

[pyc]

Big problem with ms-access MEMO field (text truncated and ends with strange āई char)

Dear,

I have encountered the following problem in Access 2002 with Memo fields

Start with a table (The_Table) with a Memo field (The_Memo_Column)
I would like to populate this column from a edit field (The_Edit_Field) located into a Form (The_Form)

When I enter a large sentence in the edit field (seems more than 512 char ????)
and When I manage the memo field through a insert SQL query The text stored in the Memo field
is truncated and ends with strange āई characters sequence

By the way, when I manage the information directly through the standard Ms-access method


Action using a SQL query

I create a insert query

The_Query=
INSERT INTO The_Table (The_Memo_Column) VALUES (Forms!The_Form!The_Edit_Field);

On click on a button, I call the query as follow

DoCmd.SetWarnings False
DoCmd.OpenQuery The_Query, acNormal, acEdit
DoCmd.SetWarnings True
Action using the ms-access standard method

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


Thanks in advance for your help !


Aloha

PYC

 

[Mile-O]

Action using the ms-access standard method

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

That line was deprecated in 1997. The replacement method is:

DoCmd.RunCommand acCmdSaveRecord

 

[The_Doc_Man]

The funky character is usually caused by having a control character or other special non-printing character in the field.

Like, if you did a cut-paste sequence to load the field from Notepad or Wordpad or (EGAD ... Word...), some of the formatting stuff, normally hidden from view, would get included in the text. Since Access is NOT a word processor package, it would not be able to handle the oddball characters correctly.

Offhand, I'm not familiar with limits on a text control bound to a memo, but you aren't using that if you use SQL explicitly. SQL is known to have some limits that sometimes can be obscure. Is there a particular reason you didn't just bind the text box to the underlying field directly?

 

[AngelsGuardian]

Glad to finaly see someone stuck at the same point as me

Anyone ever figured it out ?

 

[mccalla]

Memo Field truncating after 512

Hi,

I am experiencing a problem with a ms access report. When I try to export it, the memo field is being truncated after 512 chars and after that I get funny squares. Has anyone experience this? I see a lot of people had trouble with truncation after 255 chars but mine problem seems to be a little different.

Here is an example of what I get in excel after export:

5556677788999000--0099887766543322222222727272727277d7d7d7d77f7f7f7f7v7v7v7v7v7dhdhdhdydydydydududududududududududududuifififififififififififid8dddlld;fpflfofkfjfifjfhvnvhfmdd,sisxgsvztsvdtdfsrdfsgstywhdgcbsshyeueirorpsidjhsbw3yd7890-sosksnxbcgfhsjskau7dyfhfjdjdbbzzcfreyddcidididifigidhdhdhdhvbfgdhsjskskskduidiifuufufufufrwpoeirwporeiwpoeriwporeiwporeiwpoeriwpoeriwproiwproiwrpowirworiwporiwporiwporiwprowieoriwpdfsfsmsmf.s,fms.,mzxjsiuwoeiurwoieruwoirwoiruworiuwiruwriuworiuworiuwoiruwoiruworiuwoieruwoirwoir
then some squares appear.......

Can someone out there please help me? I am using MS ACCESS 2003.

Thanks

 

[ByteMyzer]

Ahh, this is the first time that you have mentioned exporting it to Excel. This makes all the difference.

Access table Memo fields store line breaks as two consecutive characters: Chr(13) Chr(10), Carriage Return and Linefeed, respectively.

Excel cells store line breaks within the cell only as Linefeed characters - Chr(10).

Thus, when an Access table or query containing a memo field with line breaks is exported to Excel, Excel correctly interprets the Chr(10) as a line break, but does not recognize Chr(13) as anything but a literal character. Thus it attempts to display the character, but only shows the square that you see.

 

[mccalla]

Memo Field truncating after 512

Hi,
Thanks for your reply. I checked the memo field and there are no line feeds. The squares seem to start right after the 512th char.

Why is it doing this? All the memo fields that have a length greater than 512 chars are doing the same thing.

Is there a solution out there. Please help.

Thanks

 

[mccalla]

Memo Field truncating after 512 in Report

Hi,

Ok... Let's add to the mystery.
Here's is what I found so far.
If I go into the table in DataSheet View and do a File/Export .... Save As
Microsoft Excel 97-2003....etc.
Memo Field exports to excel beautifully.
HOWEVER...... if I am in DataSheet View and do a Tools/Office Links /
Analyze with Excel - Problem occurs in Memo field.... truncates after
512 ... and I noticed it replaces squares for the remaining text.
Weird...huh?

Next, I created a query.... all it does is 'select * from table'. Same results
as above.

When I have a report, no matter which way I export File/Export or Tools/Office Links / Analyze with Excel - neither way works.

I hoping these results may ring a bell to someone with more experience with MSACESS 2003 than me.

I hoping there is a user-friendly fix for this.

Thanks in advance,
Mccalla

 

[ByteMyzer]

I hoping there is a user-friendly fix for this.

There is: Don't use Analyze with Excel; as long as you are able to export to Excel, there is no need for the Analyze function.


"Doctor, whenever I move my arm THIS way, it hurts." "Well, don't move your arm that way."

 

[mccalla]

Hi,
The "Export to Excel" is NOT working when user runs report.

 

[ByteMyzer]

The simple answer: don't try to export a report to excel if the report's recordsource contains a memo field. Limit this to tables/queries instead.

The not-so-simple answer: You can use a VBA public function to perform this task.

Create a module, paste the following code and save it as mdlReport2Excel:

Public Function XLS_Export()
On Error GoTo XLS_Export_Error

Dim rpt As Report

Dim xlsApp As Object
Dim xlsBook As Object
Dim xlsSheet As Object

If Reports.Count = 0 Then Exit Function

Set rpt = Screen.ActiveReport

Set xlsApp = CreateObject("Excel.Application")
Set xlsBook = xlsApp.workbooks.Add

Set xlsSheet = xlsBook.Worksheets(1)
xlsSheet.Name = rpt.Name

With xlsSheet.QueryTables.Add(Connection:="OLEDB;" _
    & "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";" _
    & "User ID=Admin;Data Source=" & CurrentDb.Name & ";" _
    & "Mode=Read;Extended Properties="""";" _
    & "Jet OLEDB:System database="""";" _
    & "Jet OLEDB:Registry Path="""";" _
    & "Jet OLEDB:Database Password="""";" _
    & "Jet OLEDB:Engine Type=5;" _
    & "Jet OLEDB:Database Locking Mode=1;" _
    & "Jet OLEDB:Global Partial Bulk Ops=2;" _
    & "Jet OLEDB:Global Bulk Transactions=1;" _
    & "Jet OLEDB:New Database Password="""";" _
    & "Jet OLEDB:Create System Database=False;" _
    & "Jet OLEDB:Encrypt Database=False;" _
    & "Jet OLEDB:Don't Copy Locale on Compact=False;" _
    & "Jet OLEDB:Compact Without Replica Repair=False;" _
    & "Jet OLEDB:SFP=False", _
    Destination:=xlsSheet.Range("A1"))
    .CommandType = xlCmdSql
    If Left(rpt.RecordSource, 6) = "SELECT" Then
        .CommandText = rpt.RecordSource
    Else
        .CommandText = "SELECT T1.* FROM (" & rpt.RecordSource & ") AS T1;"
    End If
    .Name = "Query from MS Access Database"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = False
    .RefreshStyle = 1
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
End With

xlsBook.Worksheets(1).Activate

xlsApp.Visible = True

Set xlsSheet = Nothing
Set xlsBook = Nothing
Set xlsApp = Nothing

Set rpt = Nothing

End Function

When the report to be exported to Excel is open, call the function from the Debug window:

Call XLS_Export()

If you know how to create custom toolbars/controls, you can even assign this action to a toolbar button: =XLS_Export()
...and assign the toolbar to your report.

 

[pmwolfemac]

Memo Field Truncate Work Around

You Could use Multiple Left / MID Functions With 255 Char. Limit....
Would Be Tedius


COLUMN 1 ***** Test LEFT: Left([tests],255) ********TOTAL: GROUP BY
COLUMN 2 ***** Test MID: Mid([tests],256,255) ********TOTAL: GROUP BY
COLUMN 3 ***** Test MID1: Mid([tests],511,255) ********TOTAL: GROUP BY
COLUMN 4 ***** Test MID2: Mid([tests],766,255) ********TOTAL: GROUP BY
COLUMN 5 ***** COMBINED: [Test LEFT] & [TEST MID] & [TEST MID1] & [TEST MID2] ********TOTAL: EXPRESSION

COLUMN 6 ***** TESTS <<THIS BEING THE MEMO FIELD>>
Make this Hidden

 

[pmwolfemac]

Memo Field Truncate Work Around

You Could use Multiple Left / MID Functions With 255 Char. Limit....
Would Be Tedius


COLUMN 1 ***** Test LEFT: Left([tests],255) ********TOTAL: GROUP BY
COLUMN 2 ***** Test MID: Mid([tests],256,255) ********TOTAL: GROUP BY
COLUMN 3 ***** Test MID1: Mid([tests],511,255) ********TOTAL: GROUP BY
COLUMN 4 ***** Test MID2: Mid([tests],766,255) ********TOTAL: GROUP BY
COLUMN 5 ***** COMBINED: [Test LEFT] & [TEST MID] & [TEST MID1] & [TEST MID2] ********TOTAL: EXPRESSION

COLUMN 6 ***** TESTS <<THIS BEING THE MEMO FIELD>>
Make this Hidden