Logo

MS Access FAQ - Reports

 Logo

Table of Contents (9 Questions)

Bullet Query Too Complex
Bullet Blank Pages
Bullet Print To File
Bullet Calculated Fields And Totals
Bullet Page Totals
Bullet Highest Of
Bullet Bold On the Fly
Bullet Path in footer selection
Bullet Stop Report if no data

Q When I attempt to generate a certain report, I get the message "Query Too Complex" but when I run the query the report is based on, the query runs OK. What does this mean?
Back to Top
A Access 2.0: Access has just exposed one of it's limitations, it's 16 bit and therefore stores data into 64K segments, you've just filled up one of those segments. Consider making your query a Make-table query or an append query to a temp table then base the report on the table, this will split the processing up a bit and as the query and report are no longer one operation, Access will no longer try to store both into the same 64K segment.

Access 97: I've not yet seen this message in Access 97

Updated: 21/12/1997 - Trevor Best

Back to Top
Q Why do I get extra blank pages between each page?
Back to Top
A You probably have extended the design surface so that it extended beyond the size of your printed page. To fix this situation, open your report in design mode and pull in the right and bottom margins as far as possible to the top or left. If the design surface is as small as possible and it still fails, check the File-Print Setup menu option and make sure that the margins are correct for your paper size.

Updated: 24/08/1996 - Mike Gunderloy

Back to Top
Q I've set a printer to print to file, however I don't want to be promted for the filename each time I run the report, I want it to always go to 'c:\temp\report.txt'.
Back to Top
A In win.ini, in the [Ports] Section, ad the the line:

c:\temp\report.txt=

Now c:\temp\report.txt will appear as a valid printer port to your printer setup in control panel.

Updated: 10/11/1997 - Trevor Best

Back to Top
Q I have a calculated field on a report (txtExtPrice, calculated as UnitPrice*Qty) and I want to sum it at the end, when I enter "=Sum(txtExtPrice)" as the formula, it shows as "#Error", what's wrong?
Back to Top
A Access reports cannot sum on fields that are calculated within the report, you can acheive the same result in two ways:

1) In the reports underlying query, create a new column (In query grid, use "ExtPrice: UnitPrice*Qty), this will produce SQL like this:

Select ITemNo, Qty, Desc, UnitPrice, UnitPrice * Qty As ExtPrice From tblItem Where ....

Then use "=Sum(ExtPrice)" in the report footer (or group footer).

2) Use the calculation in the Sum function: "=Sum(UnitPrice * Qty)"

Updated: 22/12/1997 - Trevor Best

Back to Top
Q I want a page total on my report (total of items that appear on that page only), using thew Sum() function in a text box in the Page Footer causes "#Error" to appear, what's wrong?
Back to Top
A There really shouldn't be anything wrong at all, IMHO MS left a good feature out of the functionality of the Access report engine, you can get around this with a bit of coding, you need a module level variable (this is global as far as all procedure within the report are concerend) and use some event procedures within the report. You will a text box in the page footer (we'll call it txtPageTotal).

The Report Module
(Declarations)
Option Explicit
Dim mcurPageTotalPrice As Currency

Sub PageHeader0_Print (Cancel As Integer, PrintCount As Integer)
  ' Zero total as each page starts
  mcurPageTotalPrice = 0
End Sub
Sub Detail1_Print (Cancel As Integer, PrintCount As Integer)
  ' Add to Page Total
  mcurPageTotalPrice = mcurPageTotalPrice + Me![txtPrice]
End Sub
Sub PageFooter2_Print (Cancel As Integer, PrintCount As Integer)
  ' Show Page Total Price
  Me![txtPageTotal] = mcurPageTotalPrice
End Sub

Updated: 16/11/1997 - Trevor Best

Back to Top
Q I want to show the highest paid employee per department, if I use a totals query I can get the highest salary using the Max() function yet I can't get the corresponding (lucky) employee who gets that wage
Back to Top
A You can show this quite easily in a report, just take the grouping off the query (so it's no longer a totals query), now in the report, Sort on Department with a group footer and Sort on Salary Ascending. Now make the detail section 0 cm in height (for you Americans out there, that's 0 inches :-) and simply place the data fields into the group footer.

If you really need this information in the query itself then it gets rather messy, I can of two ways:

1) Use Max() to obtain the high salary, the do a DLookup() on the table using DepartmentID and Salary as criteria to obtain the name.

2) Use Max() to obtain the high salary, Create a new query that joins the original onto the base table, join them on DepartmentID And Salary.

This may prove futile as there may be more than one person on that salary.

Updated: 16/11/1997 - Trevor Best

Back to Top
Q How can I make text on a form or report appear bold based on a condition.
Back to Top
A Assuming you know your condition (I don't, could be anything), the code would go like:

If <bold-condition> Then
  Control.FontWeight = 700
Else
  Control.FontWeight = 400
End If

Updated: 16/11/1997 - Trevor Best

Back to Top
Q I want to print the path of the current database in the footer section of a report.
Back to Top
A Use the control source:

=DbName()

In a global module use:

Function DbName() As String
  Dim db As Database 
  Set db = DbEngine(0)(0)
  DbName = db.Name
  ' do not use db.close
  Set db = Nothing
End Function


If you have downloaded and installed my add-in libray TBLIB then there's a similar function in there called "tlib_ThisDB()".

Updated: 16/11/1997 - Trevor Best

Back to Top
Q How do I stop a report from printing if it contains no data
Back to Top
A I haven't checked yet but I'm told that there's a NoData event in Access 97 where you can Cancel the opening of the report, in the meantime for version 2.0 users:

Sub Report_Open(Cancel As Integer)
  If DCount("*",Me.RecordSource)=0 Then
    MsgBox "No Data for this report", 16, "Sorry"
    Cancel = True
  End If
End Sub


Be advised that this will trigger an error in the code that opens the report so make sure you have error trapping code, macro users will need to re-think their strategy on writing database applications, a macro with an error will just bomb out.

Updated: 16/11/1997 - Trevor Best

Back to Top