Certain fields get corrupted

[MarDude]

Your solution would work, but it would require several steps. The reason we export the report to Excel is it already exists. In other words, users are already looking at the report. so what do we do? Add another button to the selection criteria instead of preview to export. Then they have to look at the report. Close the report. Run the query again. That will not go over well. Users generally do not like to have an extra click added to every step of thier workload. So adding two extra clicks AND a wait for a query is going to make everyone who uses this function unhappy.

So while your solution technically works, it comes up short in a business sense. Not your fault since you didn't have all the details, but thanks for trying.

Remember these are the same users who complained about seeing the quotes. While your solution fixes this, it introduces more work.

I mentioned to them I could write my own export routine. Its not very hard to copy query results to Excel. I can just populate a dataset. ADO has functions to do just that. But then they have to learn a new way to do their job. And I don't blame them for not wanted to get used to something different. sometimes it can be difficult to see things from a user's point of view. They want it to work the way they are used to or not at all. And depending on what they do with the spreadsheet, it may be the best thing for the bottom line.

[June7]

The reality is you and users might not have any choice as you are stuck with 2003 and stuck with Excel output. I think it could be done so that new behavior is not so different nor overly burdensome to users.

Report has Click and DblClick events (I've never used either). So instead of right click, left click on report. However, it is possible those events will fire only in ReportView, not PrintPreview.

DoCmd.Close acReport, "reportname", acSaveNo
If MsgBox("Export data now?") = vbYes Then
'code to export query
End If

What event opens the report - a button Click? Can open report with acDialog parameter. This will suspend code execution by the calling procedure until report closes. Then the procedure could continue with the above If Then.

If people could never get used to something new there would be no progress.

[MarDude]

Your way would work, but it would inconvenience the users. Users don't like extra clicks and I don't blame them. Who wants their job to be harder? While your technique would work and fixes the problem of quotes in the report, it would require them to back out of the report, run the query again (which may or may not be fast). The reason the report is exported to excel is the users are already looking at the report. They need to see they have the right BOM (Bill Of Materials). Some of the part numbers look very similar. I told them I could write my own export routine. It wouldn't be much work since ADO can create a dataset and it is easy to dump the dataset to Excel. But that would make them have to learn a new process, and I can't blame them for not wanting that. They have much more to do than just copy a report to Excel. Remember these are the same users who rejected my adding quotes to the report which to me made more sense than an incorrect itemid. They will definitely balk and anything that makes their job more difficult

So basically if I can't fix it so it can work as before I don't have to do anything. Fine by me. My job is to make them as happy as then can be.

[June7]

Don't understand what you mean by 'run the query again'. SELECT queries don't have to be 'run' unless they have popup input parameters (which I never use because they are an aggravation source for users, unvalidatable, and annoy me).

Let's get really nitpicky and count clicks:

1. open report
2. right click
3. pick item from menu
4. X close report

versus

1. open report
2. left click or X close
3. message box or export command button


Good luck, hope you find a satisfactory solution and share it here.

[MarDude]

You must've missed the solution I already posted, maybe I did not explain it fully.

I used double quotes.

Problem:
Using the send function found by right-clicking on an Access report to attach an Excel spreadsheet to an e-mail causes data corruption.

Result:
A text field in the format of 20####-00# will be treated as a date. Excel will do some sort of conversion. For example 202103-001 becomes 1313049 and 205103-001 becomes 2408777.

Solutions:
1) Enclose the field in quotes.
2) Prefix the text field with another character besides space.
3) Write your own custom export function.

[June7]

I thought the users rejected that solution and you were back to square one. Oh well, as long as you now have it working, congrats.

[Rawb]

I realize this is marked as Solved already (and that all my other suggestions were shot down!), but I have one more suggestion that mirrors your Solution #3:

Remove the option for viewing a Report on the data. Replace it with an option to "View this Report in Excel" that runs your custom export function, generates a .XLS file, and then opens it for the user.

This would allow you to control the output to Excel (by either using Office Automation to force the column's Format or by using a single quote prefix) and give them a pretty report to view. And, at the same time, they would already have their Excel file for sorting and filtering.

[MarDude]

The users did reject my solution. But just because they reject a solution doesn't mean it isn't a solution. And if it is a solution it can help others.

Good luck, hope you find a satisfactory solution and share it here.

that is why I reposted it. It seemed you had trouble finding it.

@Rawb
Don't feel bad, my solution was shot down also. But I like your idea. It allows then to see their report and I can use the single quote thing you mention earlier without interfering with the output. At least I can't think of anything that can complain about. I'll see what they say.

Thanks