By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

Exporting report from Access to Excel creates empty rows

P: 1
I have a report in Access that has a lot of grouping (sometimes over 5 group headers). When I export it to Excel where more people can use it and are familiar it creates a lot of blank rows when there's nothing inside the group. Certain items in the report will have items in all 5 group headers/levels, most will not. I have a macro in Excel that deletes the blank rows but some reports are too long to export to Excel because of the blank rows. Is there something simple I can do in the report design so that it doesn't create a blank row in Excel? I like the group/outline thing it does when I export the report, just not the extraneous lines. Please help!
Sep 17 '07 #1
Share this Question
Share on Google+
1 Reply


puppydogbuddy
Expert 100+
P: 1,923
I have a report in Access that has a lot of grouping (sometimes over 5 group headers). When I export it to Excel where more people can use it and are familiar it creates a lot of blank rows when there's nothing inside the group. Certain items in the report will have items in all 5 group headers/levels, most will not. I have a macro in Excel that deletes the blank rows but some reports are too long to export to Excel because of the blank rows. Is there something simple I can do in the report design so that it doesn't create a blank row in Excel? I like the group/outline thing it does when I export the report, just not the extraneous lines. Please help!
In my opinion, the easiest and best way to prevent the blank rows is to place the necessary criteria in the criteria row of the query grid to exclude the unwanted rows from the query results that is the record source of the report, Excluding them from the query results will automatically exclude them from the report and from being exported from Excel,

As an example, suppose you have a report called Accounts Receivable Balances, and you only want to show customers whose balance is not equal to zero. Going to the Balance Column on the query grid and placing the following criteria>>>>>> <> 0 will automatically exclude any customers with zero balaces from the query, any report or exported data based on that query. Other criteria typically used, include: Is Not Null; nz([columnName], "") <> ""
Oct 5 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.