Export to Excel Report Problem

[Garridon]

I have two reports which are used for exporting to Microsoft Excel. All the fields export properly except one. It's a memo field, and it cuts it off at exactly 126 characters. Anything that runs under 126 characters is fine; anything over doesn't carry over.

I tried changing it to a text field, importing the data into a new field, and even importing the database into a new database. It still does the same thing in all the reports.

Any idea what's going on?

Thanks!



Linda Adams
Garridon@aol.com

http://www.geocities.com/hallona_2000

I'm a professional writer, published internationally.

 

[DougP]

this is a crude fix but...
Try splitting the long field up into 2 or more that are less than 125 characters.

this is how: create 2 functions in the modules TAB

Public Function LeftSide(MyLongString)
LeftSide = Left(MyLongString, 125)
End Function

Public Function RightSide(MyLongString)
RightSide = Mid(MyLongString, 126, Len(MyLongString) - 125)
End Function

then put two text boxes on your report one looks at the Left the other looks at the right
Longfield = the field on your report that has more than 126 characters

in the text boxes "control source"
=LeftSide([Longfield])

=RightSide([Longfield])




DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[MichaelRed]

Well, this "gets" up to the 250 characters, but memo fields could be considerably longer than this.

I certainly don't have time to do it at the moment, but what is really needed is to get all of the memo field into a set of variables (array of strings?), then place UBound(Array) + 1 'text boxes' on the form and populate each with one of the array elements.

Parsing the memo field is 'easy',
Dim Idx as Integer
Dim Jdx as Iteger
Dim txt() as String

Jdx = 0
ReDim Preserve txt(Jdx)

For Idx = 0 to len([MemoField]) step 125
[tab]txt(Jdx) = mid([MemoField]), Idx, 125)
[tab]Jdx = Jdx + 1
[tab]ReDim Preserve txt(Jdx)
Next Idx

ReDim Preserve txt(Jdx - 1)


To do the multiple text boxes on the report, it is necessary to construct one manually and set it's index to 0. This "makes" it an array. During the report generation process, create additional text boxes (createcontrol method) and adjust their positioning to prevent overlap/hiding.

Of course, if your memo fields never exceed 250 characters, this is totally over-kill.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Garridon]

Now I'm confused. I have other text and memo fields in the same report that work fine. In a form, I can view all the data entered in the field and edit it. In an Access report, all the data is prints out. It's only when I export to Excel that it cuts off the data at 126 characters in this one field.


Linda Adams
Garridon@aol.com

http://www.geocities.com/hallona_2000

I'm a professional writer, published internationally.

 

[MichaelRed]

Linda,

based on your last post, I 'checked' Excel (not one of my favorite areas). I was able to create text in a single cell which was ~ 23K "Characters", so the issue would not appear to be with an excel cell holding the content of a (normal) memo field.

Your original post mentions exporting reports to Excel. I am not familiar with this, as I have always exported the 'recordsource' (for those who are soooooooooooooo backward as to LIKE using Excel), and generated any 'report' based on the data exported. Using this process, I have not encountered the problem of field truncation - although I do not often include memo fields in the transfer.

I have encountered the truncation effect in Ms. Access forms/reports, and was only able to overcome it by deleting and recreating the textbox. This appeared to be some strange interaction with having assigned the textbox control source in 'the wrong way'. I have not encountered this in a while but - I think - it was that you could only get the text box to display the full (e.g. > 125 Chars) by adding the field to the report from the fields pop-up menu.

My post - re the multiple text boxes - was only meant to clarify DougP's post. If you create multiple text boxes to hold the memo field, you need to create enough of them to hold whatever the length of the memo field happens to be - not just two of them.

BTW, are you really exporting the Ms. Access REPORT to excel?

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Garridon]

I did try to remove the field and insert in back in using the View Field List. Unfortunately, it is still truncating the field. It does this on three separate reports, and it's always one specific field. I tried inserting a new text field and pasting one 126 plus text into it to test it, and the new field did exactly the same thing. I also tried linking through code with another field (=[strItem]&" "&[strSource]) to see what it would do, and if the second field made it run past the 126, it truncated it as well. Yet, it still shows up fine in Access reports. However, Excel is what the user wants ...

I'll just have to keep working on it, and hopefully I'll find a solution soon.



Linda Adams
Garridon@aol.com

http://www.geocities.com/hallona_2000

I'm a professional writer, published internationally.

 

[MichaelRed]

Linda,

Sorry to be a pest, but could you clarify one point for me? Are there other memo fields which exceed 126 characters that do export properly (not truncated) to the excel format?


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Garridon]

That's what is so puzzling. I have one text field that exceeds the 126 and two other memo fields, all of which export fine. It's just the one field that has the problem.



Linda Adams
Garridon@aol.com

http://www.geocities.com/hallona_2000

I'm a professional writer, published internationally.

 

[Shep]

Just a thought, but could it be the actual data in the field that is causing the problem rather than the field itself ?
For example, does it contain a carriage return / line feed combination (at position 126) that is stopping Excel displaying the data correctly when you come to view it ?

Shep

 

[Garridon]

Actually, it appears to be a design issue in Access, though I can't explain why it's only going to 127. According to ACC2000: Memo Field Truncated When Report is Output to Excel (updated November 4), it does truncate any fields that are over 255 characters when exported to Excel. They have a solution; Part I requires me to set up a report that splits the fields in question into text boxes:

=Mid[memNotes]1,250
=Mid([Notes],251,250
=Mid([Notes],501,250

Unfortunately, it would also require the user to concatenate everything back together again using a formula. The user has only basic computer skills, so I'm probably going to have to look for another solution.


Linda Adams
Garridon@aol.com

http://www.geocities.com/hallona_2000

I'm a professional writer, published internationally.

 

[MichaelRed]

Linda,

I don't understand the part about the user needing to concatenate the fields back together. I never quite understood the exporting of the "Report". Is the Report exported, or does the datasource of the report get exported?

If it is the "Report", what format does it take? Does it "look like" the report - when you view it in Excel?

If it is the datasource (fields) for the report, are they seen as a linear set of "Cells"? If it is the latter, does it include a "header" row? this would give most Users the clue that they collection of cells is really just a long field. --- Especially if you look at my first post in the thread. This would "name" all of the fields the same - with an index to indicate the order. I believe that the Export would also place the all adjacent to each other for you.

At least give it a try. It is quite easy to implement and may be sufficient.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Garridon]

The report itself is being exported. I've been playing around with the format to make it export with the least amount of work the user has to do. I had to make sure that all the fields themselves (not the labels) were called what the column headings would be in the Excel version. I also had to make sure that in the individual field properties that the field would grow; otherwise it would not do a word wrap in Excel (meaning the user had to manually do that). The fields are laid out on the Access report as follows:

Field #1
Field #2
Field #3
Field #5

When it exports, each of those fields becomes a column in Excel. The title of each column is the field title. It actually does not look like the Access report at all (labels and page titles aren't exported with it--just the fields). Unfortunately, when Access (2000) exports, it does it in a Microsoft Excel 95 format, which does not accept the longer text fields. Even if I use the File, Export and choose Excel 2000, it still does it in the older format, truncating the text. When I save it, it gives me a message box saying that this file is in an older version of Excel--do I want to save in 2000.

So I'm working on an Excel macro which will run the formulas and the other formatting. At least until Microsoft fixes this issue.




Linda Adams
Garridon@aol.com

http://www.geocities.com/hallona_2000

I'm a professional writer, published internationally.

 

[MichaelRed]

Linda, Since the export of the 'report' only exports the 'data', you could just export the record source (or what ever portion you want the user to see). I have never tried to ecport a "report", but have done some work in exporting to Excel - using 'recordsources' (queries). Using the alais capability of queries gives me a lot of control over the column headings and formatting. The memo field(s) issue could reasonably be handled in a function, to generate the necessary number of additional fields - in a seperate recordset which is joined to the original source. As long as you could put the collection ofthe memo fields at the 'end' (right) of the other info and assign meaningful column headings, it could be not much of a hassle for your users.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over