Old Tricks in Access
By James D. Duncan, CPA
I thought this month I would share 3 tricks I have picked up over the years. I am sure I gleaned these from the Access SIGs taught by Blaine or Boyd Trimmell. The people in the trenches can tell us much more than the vast volumes of books and magazines. Experience is that way.
First tricks are for the page footer of your reports. I usually let the Wizard build the report and modify it from there. However you do need 3 things on the bottom of the pages; date and time the report was run, the name of the report and the page number. The report Wizard handles the page number fine, half-way handles the date and time and needs help with the report name. Some day I intend to build a report Wizard that will do these things automatically. Along with cleaning the garage, sorting my old magazines, and…. well you get the idea.
On this report in the Page Footer section I have added a text box and changed the source to read =[Name]. The format is Long Date because I copied it from the field to the left of it, which is a date. Access ignores this and displays the name you assigned to the form. I then make the field a reasonable size and run it so I can see that all the text is displayed. For example it will read rptFacility when this is run. The reason to put the name on the report is so when the user refers to it you can ask them the name in the middle on the bottom so you have a common name for the report.
Now right click on the field on the bottom left in the Page Footer. Select Properties and you will see it has the Long Time selected in the Format of the text box. Click on the button to the right and select the General Date. This prints the date and time and now multiple reports from the same day can be distinguished.
Run the report again to see that the field will all display. Tidy up the spacing as needed. I think you will find these 2 items in the footer very valuable in avoiding confusion down the line.
Now for more of a programmer trick. You do not need to be a programmer to do this; it is just that sort of thing. Fields that are used for running sums have their visible properties set to No. The best definition of running sums is from Microsoft Help. A portion is shown below.
…….The RunningSum property specifies whether a text box on a report displays a running total and lets you set the range over which values are accumulated. For example, you can group data by month and show the sum of each month's sales in the group footer. You can show the running sum of accumulated sales over the entire report (sales for January in the January footer, sales for January plus February in the February footer, and so on) by adding a text box to the footer that shows the sum of sales and setting its RunningSum property to Over All.
If you use Running Sums this will make sense to you. The trick is this: You cannot tell by looking at a field that it is not visible on the actual report, UNLESS you set the Border Color property to 255 (red). Today you know you set this field to not print. A month from now you will not remember, unless it shows up with a red border in the design view. For example, note that the left two items in the detail band have red borders. This tells me the visible property is set to No and thus does not print on the actual report. Just a mental reminder that these fields are NOT detail items.
Hope this adds to your knowledge. We have consolidated all of the Access beginner, Access advanced, SQL and VB.net SIGs into one. This is done because of dwindling attendance. I think the lower attendance is due to the comprehension that these products are much more complicated than first anticipated or advertised. Like I always tease Blaine and Boyd, they make the products look much easier because you cannot see the 20+ years experience behind the keyboard. Come to the SIGs, add to our expertise.
James is a member of the OKCPCUG and an expert in Access and Excel. James can be reached at BJ4DUNCAN@aol.com