Exporting to Excel not working if report has an #error in one of the fields.

[metronj]

Hi All,

I am trying to do some very simple data exports into Excel sheets utilizing a report format I created and a couple of fields. I use the Filter & Sort menu to get the data I want on screen and then simply right click on the report -->export to Excel.

The problem is that when I have any sort of Error in one of the fields, it won't export at all. Some of my fields are calculated (very simple stuff like profit margin), and if I don't have a value entered Access will put a #Error instead of an actual result. It seems that when I'm doing the export if it finds one of those it just stops the export dead in it's tracks.

I have tried doing this with data that is clean and it works perfectly however, I might not always have clean data and it is fine if the field has the #Error in it, I do not care as I can always go back later and correct it.
The other strange thing is if I go to the Query that I'm using to generate the data for the report and do the export from there, then it does it with no problems, #error or not. So why won't it do it from a report as well?

The reason I want to do it from the report view is because I'm grouping 6 reports together, filtering on a high level field (model in this case) and then I want to do the export one shot instead of having to do it 6 times.

Here is a snip of the data, If I filter out the fields with #Error it exports no problems. With the #Error it refuses to work. Any help would be greatly appreciated!

Thanks and have a great new year!

[imgnothumb]http://bytes.com/attachment.php? attachmentid=74 00[/imgnothumb]

[zmbd]

Export data to Excel

The report is actually evaluating the data at export. Because you haven't provided a means to handle the error exception, neither Access nor Excel know what to do with the data.

It's one reason I try not to export reports to Excel. There are also some other little nasties that can happen with dates and other formatted numbers such as telephone or SSN.

For subreports without data:
I see #Error displayed in a control ACC2003

You can always post your control's formula, please format it using the [CODE/] button. There may be a means to correct for the error.

[NeoPa]

This is going to sound obvious, but you need to write the formulas so that they never have errors in them if you want to export them via a report.

It's not a bad idea to get into that habit for all formulas anyway, but it's necessary if you want to export a report.

Like Z, I never export reports or forms that way. It's just too clumsy. Nevertheless, it's possible so if you want that you just need to design the report correctly and it should work fine for you.

If you have any difficulty with any formulas then feel free to post a question about them. In this case feel free to post it in here as it's what the thread is fundamentally about so won't be considered to be hijacking the thread.

[metronj]

Hi Guys,

Thanks for the response. I am writing the formulas simply as such:

DealerNet: FormatCurrency([Price]*[Disc])

Is there some way to write these so if there is an error it simply makes the field a null or zero perhaps?

Thanks for looking at this!

[zmbd]

Try:

DealerNet: FormatCurrency(Nz(([Price]*[Disc],0))

This will take care of a null

[metronj]

Yep, I get it. Nz to the rescue yet again lol. That works great.

Thanks zmbd, much appreciated as always!

Have a great new year everyone!