I am creating a report using data from two tables: Customers and Orders. The Orders table has multiple records per customer ID. I only want to pull into the report those records that have the highest OrderID per customer along with realated data, namely address information for the customer as of their last order. I know that I can group my records by CustomerID, place records in the detail section and supress, then create a summary (Maximum(OrderID,CustomerID), and display just this in the group summary, thus yielding just the data associated with the highest OrderID per client, but I am trying to create labels using the label wizard for these records (which as far as I know requires the data in the detail section) and so that method will not work for me.
When I try to create a selection formula that I thought would work such as the following:
I get the following error message: The formula cannot be evaluated at the specified time, or if I remove the "whileprintingrecords" function, I get "This function cannot be used because it must be evaluated later."
Any ideas how I may be able to do this? The end result would ideally be:
1. No groups
2. The address associated which each client's last OrderID in the detail section of the report (to create labels)
3. I do not want to use SQL commmands because the end user cannot support.
Any ideas are greatly appreciated.