Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from Integrating Excel and Access

Using an Access Table for Reporting

While you can do all of this reporting without putting it into a table, I have found that by using a table to house report formats, you can make changes without touching the code. This makes it easier to develop reporting applications and allow others to maintain them. How much you put into the report formats is up to you. I suggest having fields available for font parameters, number formats, formulas, and a true/false field to allow you to hide the row. Let's look at the merits of using a table.

Assume that you wrote code that builds an income statement report with about 30 lines and several sums. At the bottom, to come up with net income, add income subtotals together and subtract expense subtotals. Of course there are underlines, double-underlines, and other formatting, as well as formulas. This report works well for a while but after five or six months in production, someone wants to add a few lines to the report to break out some expenses. If you did all of this formatting in code, you would have to edit just about every line that has to do with formatting, formulas, etc.

Now, if you have a table that holds all of the instructions, you would just need to put in the lines that you need, which would hold the formatting that you wanted for those rows. You then might have to update the formulas in the table to account for the number of new rows. I have found that it is much easier to teach someone how to update a formula in R1C1-style notation ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page