How can I display "last sale date" in a Text Box on a Form ?

[mthornblad]

Hi

I have a Products table and a Sales Detail table that have a
relationship based on the Product Number. The Products table has the
Product Number, description, etc.. The Sales Detail table has the
Product Number, qty, price, and Sale Date.

I have a form where I enter a Product Number. I want to have a text
box that displays the "Last Sale Date". That is, the latest date in
the Sales Detail table with the Product Number I enter on the form.

Please let me know how to do this.

Thanks in advance
Mark

 

[Allen Browne]

Put something like this in the Control Source of the text box on your form.
(It's one line.)
=DMax("Sale Date", "Sales Detail",
"[Product Number] = " & Nz([Product Number],0))

If Product Number is actually a text field, you need more quotes. It's the
same as for DLookup(), explained here:
http://allenbrowne.com/casu-07.html