I have an inventory report based off of a query. The report shows a starting
and ending inventory volume for each day of the month (or whatever parameter
is entered). In the page footer, I have total amounts, and allowable losses.
In this section, I need to have a text box to display the starting inventory
amount for the month (the first value shown in the report column/query), and
another showing the ending amount (the last value) as a reference for the
user to see, then make another calculated text box from these values. I
believe I need to use a DFirst & DLast expression, but am not sure of the
proper syntax. As always, this newbie thanks you for all of your help.
Well, no, you don't want to use DFirst or DLast ...... ever!
See:
Microsoft KnowledgeBase article 208190
ACC2000: First and Last Functions Return Unexpected Records
To display the first and last record values on a page (as in a
Dictionary or Phone Book format):
Note... the below information refers to "Inventory".
Substitute your actual field name in it's place.
The PageHeader has access to the first detail record.
So to show the first record on each page all you need do is to place
a control bound to the [Inventory] field in the header.
Whatever the first detail record on the page is will be shown in the
Header.
To show the last name in the Page Header requires a little work.
Add a new table to the database.
ID Field (AutoNumber No Duplicates)
FinalAmount(Number, Long Integer datatype) *
* Change this datatype to whatever your actual [Inventory] datatype
is.
Name the table 'tblPageHeader'
Open the table in Table View.
Enter a 0 (Zero) in the [FinalAmount] field.
Continue adding records for as many pages as you expect the report to
have by adding a 0 to each record, incrementing the ID field by 1 for
each record,
So if you expect 500 pages, make 500+ records.
(This can be done using code, but that would be another post.)
You now have a table with over 500 records and the [ID] numbers are 1
to up to the number of records you have added.
In the Report, add a control to compute [Pages].
If you don't already have one
= [Page] & " of " & [Pages]
will do.
Then add a control to the Page Header where you
wish to display the final name on the page:
=DLookUp("[FinalAmount]","tblPageHeader","[ID] = " & [Page])
The Page Footer has access to the last Detail record.
Code the Report's PageFooter Format event:
CurrentDb.Execute "Update tblPageHeader Set FinalAmount = " &
[Inventory] & " Where [ID] = " & [Page], dbFailOnError
Run the report.
The [Pages] control forces the report to be formatted twice.
On the first pass, the table is updated after each page with the final
[Inventory] value on that page.
Then, on the second pass, the report is displayed and the DLookUp in
the Page Header control reads the corresponding page [FinalAmount]
value from the table.
