Last Contact Update Query

[JimmyG]

My database contains, amongst other items, a companies form and a orders form. I have a field on my order form to insert the order date. On the Customers form I have a filed called date last ordered.

I need to be able to fill the date last ordered field with the most recent date from the orders form (some customers order several items each week). I have attempted this using an update query, the query returns a date which is correct for the customer but isn't necessarily the most recent date. The problem only seems to occur with customers who make lots of orders.
How can I make the update query return the most recent record?

If it helps, each order has an order number, the most recent date,i.e the one I want will therefore have the highest order number

Does anyone know what I am doing wrong?

 

[Pat Hartman]

There is no need to store the date in the customer record. Look it up as necessary. Use a DMax() as the ControlSource for a control on the customer form.

 

[JimmyG]

Thanks for the advice and I'm sure it would work if I could write it properly! I've never used DMax() before, I've had a go but the same date displays for every customer, the date displayed is the last order date of any customer, i.e. it is not specific to the individual customer.

=DMax("[OrderDate]","Orders")
This is what I've inserted into the text box on my customers form,

Could anybody correct it?
The field I want is called OrderDate and the table it is in is called Orders, I want the most recent order date to be displayed for each customer.

Also am I just supposed to type it into the text box?
I appreciate any feedback

 

[Pat Hartman]

Domain functions have a third argument which you have omitted. You need to specify WHICH rows you want analyzed (if you don't want to analyze the entire domain) by including selection criteria.

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

The above example would limit the domain to only the orders for a particular customer. change the names if necessary.