Using DMax to display customers most recent order dates

[JimmyG]

My database contains an orders table and a customers table. Each time an order is placed a different order date is entered. I need the most recent order date to be displayed on the customers form. I originally attempted to do this using an update query but was advised to just display the most recent order date on the customer form using a DMax control, as the most recent order date will also have the highest OrderID.

I attempted this but the date returned for all customers was the most recent order date in the database, i.e. every customers date displayed the same date. I need the control to display the most recent date for each individual customer

This was what I entered into the text box

=DMax("[OrderDate]","Orders")

Could someone correct this so the text box displays the most recent order date for the specific customer?

The field containing the date is called "OrderDate" and is contained on the "Orders" form. i want the text box to be displayed on the "Customers" form.

I also need to be able to use this date in a query to return customers who haven't placed an order for over a specified number of days, will this still be possible using the Dmax method?

Thanks for any advice.

Jim

 

[DBL]

I would get the data I wanted through two or three stages. Firstly I would create a query that pulls out the Customer ID and the order date and sort the records by Order date. I would use this query to create a second query and Group By Customer ID and the Max of the Order Date. I would then link this query to the query my form was based on by the Customer ID and pull the MaxOrderDate field in to my query grid for inclusion on my form.

HTH

 

[Mile-O]

=DMax("[OrderDate]","Orders","[CustomerID] = " & Value)

 

[JimmyG]

Cheers DBL thats sorted the problem out!

Thanks again

 

[JimmyG]

Damn it!

Seems it hasn't worked as well as I thought
The date displayed is correct for every customer but I can no longer add records to my Customers table using the form.

I have traced the problem and it appears on the 2nd query where the max order date is displayed with the customers names, this query doesn't allow me to make edits or additions.

Is there any way to solve this or am I back to square one?

I also tried going back to DMax using the following recomended formula

=DMax("[OrderDate]","Orders","[CustomerID] = " & Value)
=DMax("[OrderDate]","Orders", "CustomerID =" & CustomerID)

Neither worked.

 

[DBL]

Sorry about that. Another way might be to create the various queries to get the MaxDate you want and then to use a DLookup on an unbound text field on your form to view the date:

=DLookUp("DateField","QueryName","CustomerID = [Forms]![YourForm]![CustomerID]")

I'm sure there's probably a tidier way to do it!

 

[JimmyG]

Yet again you solve my problem!

I'm not bothered if theres a tidier way, it works and thats good enough for me!

Thanks again