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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mthornblad@gmail.com - 24 Jul 2007 06:49 GMT

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

Reply to this Message

Allen Browne - 24 Jul 2007 07:03 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I have a Products table and a Sales Detail table that have a
> relationship based on the Product Number. The Products table has the
[quoted text clipped - 9 lines]
> Thanks in advance
> Mark

Reply to this Message