Finding values (Access2K)

Topic

New Reply

I have a main form with a sub form. Main form contains customer date, subform contains history of orders data. I need to query the subform (based on a many table) to return the highest amount paid, along with the date it was paid. I can get the highest amount, but am having trouble also retrieving the date of the highest amount. They are both in the same row of the history table.

Thanks for any help….

Reply | Quote

Replies

If I understand your arrangement correctly, here are a couple possibilities: (1) Use the recordset object’s findfirst method to get to the record (row) that contains the highest amount paid; then reference the date in that row. (2) A little “kludgier” (and slower!) but doesn’t rely on the recordset being open: Use the DLookup function with the highest amount paid (which you’ve already gotten) as the criterion to find the record of interest and specify the date as the value for the function to return from this record.

What if the highest amount paid occurred on two or more dates? Do you care which date is returned? If so, this gets a little trickier.

This is a little sketchy, but hopefully gives you some ideas. If you need more, holler!

Reply | Quote