Certain fields get corrupted

[MarDude]

I can't make sense of this. I also do not know if this should be in the Excel section. Perhaps if no one knows I could make a new thread in Excel .

Using Access 2003.

The problem occurs when I right-click an Access report, then click "Send to" which has only one option of "Mail recipient". Then I click Excel. An outlook window containing an out going mail opens with an Excel attachment. There is one column with item ids. some get corrupted while others do not. It seems they need to begin with the numerals 20 to get corrupted. Anything else comes out OK

For example, the following item ids are ok
100657
100657-17
216116-115
221007-001
The following get corrupted (the corrupted version follows)


202103-001 becomes 1313049
205103-001 becomes 2408777

Anyone have any idea whats going on?

[Rawb]

The data's not getting corrupted, it's getting converted. In this case, into a date.

If you're using Office Automation, you can set the column to "Text" (instead of "General") before importing the data. Otherwise, you'll need to prepend a single quote (') to the beginning of each field when you export it. This will tell Excel "Don't convert me! I may LOOK like something else, but I'm actually a text string!"

That should solve your problem!

Oh and it was an Excel issue

[MarDude]

No automation, I'm using 100% Access functionality. I'll state it again. I right-click on a report and get a menu. On that menu is a "Send..." option. The next window has choices like HTML, Excel, etc. Clicking Excel opens up a new message in outlook with the output as an attachment. I could add a quote but then users will complain when they see all these quote marks in their report. I believe its only an inward facing report but if its not then that's another issue. First I'll try prefixing it with a space and it that doesn't work I can try non-printing characters like ctl-a. If that fails then I guess I can enclose the whole id in quotes so at least it doesn't look lop-sided.

Thanks for the help

Mar

[Rawb]

The single quotes (in the Excel Spreadsheet) won't be visible to the user. Excel will use it to flag the data as a string, but hide it from view (unless they look in the Formula bar at the top).

Also, you may be able to just enclose it in double quotes then. That should be an option you can set when exporting.

I understand your frustration though. In the databases I maintain here, I've moved them all to Office Automation so I can control that kind of formatting, but it's still a pain to set it all up.

[June7]

Rawb, OP is using SendTo method to attach report as Excel file to email. There is no export to a saved Excel file, no way to edit the Excel output. I don't see any way to delimit text with SendTo method.

MarDude, do recipients really need Excel file? Upgrade to Access 2007/2010/2013 and the report can output as a pdf attachment to the email.

[MarDude]

Thanks for helping to clear that up. The Send also can create a HTML, Text or Word doc. So I must presume that the users want the Excel whether they need it or not. The software also does not support anything other than Access 2003. This is part of an ERP package and I'm waiting for the vendor to tell me what I need to customize this report.

[June7]

What software? How can report exported to Excel be useful? Maybe should export a table or query.

Since they aren't receiving an Access file, why does it matter if you use newer version?

There are alternatives to the right click SendTo method, at least there are in 2007/2010/2013. They involve VBA code.

[MarDude]

This is part of a large ERP package. It is written in .Net and uses Access for some functions. It is highly customized and integrated and supports Access 2003 ONLY. I am just a programmer, I produce results. I improve the software to help the business but do not micro-manage the users. I only need to know the high level functions of the business. I do not need to know what the everything the users do with their reports. That would be counter productive.

But I can answer your question since I am familiar with both Access and Excel. Excel allows sorting and totaling for starters. Users can control what they see without the need to know SQL or bothering the programmer. That's a win-win. Can't do that with a report. And that is just scratching the surface.

Anyway, I tried prefixing the field with a space but that did not work. I also tried a non-printing character (ctl-A) but that produced a box. The quote suggestion worked so now I will pass it along to the users for them to decide which they prefer.

Thanks

[June7]

Got it - .Net is working with the Access tables and data export is needed. Just not sure how useful a report output to Excel can be. Reports usually don't export to a nice tabular structure so 'sorting and totaling' is often not feasible.

You are prefixing data with apostrophe (or quote) in the Access table?

[MarDude]

I used double quotes. The users hated it and I had to back out the change. Good lesson for me anyway. And if they change their mind I have the solution.
Actually the reports export quite nicely. Here is an example if you're interested: Multi-Level Bill.zip. That is a small one, some of them can get quite large but export the same.

[June7]

Must be a very simple report. Reports with grouping & sorting and/or subreports might not be so neat. I have seen some very nasty output.

Odd, I see some numbers under Field64 with two decimals: ..2

[MarDude]

Simple is the ideal goal. Programmers often make the mistake that complex is good, that is simply untrue. I know I am a programmer and have learned. Simple reduces the companies cost in the long run. Everything should be simplified as much as possible. Complex does not necessarily equal useful. I can pull some very useful information out of the database and it would appear in one line.

Odd, I see some numbers under Field64 with two decimals: ..2

I don't really like the format of the report but the users do. Those are not decimals that you see. They are nesting levels when one part is build up with many parts. Think of the level in a tree control. That's what the number (and number of decimals) mean. Again, I would not design a report like that unless that was the spec.

[June7]

So you still have the issue of modified data? Did you do a test of export from table and query just as a check? No idea what would be special about values beginning with 20 that would cause this. If you want to provide data for analysis, follow instructions at bottom of my post.

[MarDude]

Excel "thinks" those are dates. Since it is entirely microsoft's code I can't do anything about it.

The following get corrupted (the corrupted version follows)
202103-001 becomes 1313049
205103-001 becomes 2408777

I tried some math on the spreadsheet and got some interesting results.
2408777 - 131349 = 1095728
The only thing I've found the corrupted numbers to have in common 20#### is the current year, 2013, so using this logic:
2051 - 2021 = 30
30 * 365.25 = 10957.5
Coincidence? I tried some other numbers and while the results are not as close as the example I used they are close enough for me to believe Excel thinks the numbers are in some kind of date format begining with the year. Prefixing the filed with an * stops this conversion as does a ctl-A or putting it in quotes. So according to my tests, even though the field is defined as text in the table Excel is trying to convert the data into a date. Since the report uses a temporary table it would be a bunch of steps to test going to another table just to end up with a maybe answer slightly stronger than the maybe I have now.

[Rawb]

I'm going to jump all the way back to the beginning, so please fell free to correct me (or yell at me!) if I get something wrong...

Is there a reason you have to export the Report itself to Excel? Would it be possible to export a Query instead of a Report, provided all the required data was included?

If so (and this is getting back to my original post about the single quotes), you can make a Query that prepends the single quotes to any fields that suffer from this conversion issue. This would allow the Report (which would be based off of the original, unaltered Query) to display normally while still giving you proper output when it's exported to Excel.

Make a copy of the Report's underlying Query.

• Change the "Field:" value (for each column that is getting improperly converted) as follows.
  
  • FormattedData: "'" & [MyOriginalData]
  
  
• Save the changes to your new copy of the Query.
• Export the copy of this Query instead of the Report.
• Export this Query

This would also allow you to leave the original data itself unchanged.