Dlookup, Return last value

[Edgarr8]

Hi!

I would like to use a text box that would return the last value entered.

I have a table that keeps the following information.

Rep, Date, Product, Amount

so James, 5/20/09, Candy, $20

Joe, 5/21/09, SOda, $5

James, 5/21/09, Soda, $21

Is there any way that on the entry form i Can add a Dlookup that can go back to that table and bring back the "Amount" of the "Rep" on the last day they sold something?

Here is what I have

=DLookUp("[Amount]"," Sales","[Rep] ='" & [Rep] & "'" & "And [SaleDate]= last[SaleDate]")

But i get an Error...is this even possible?
Any help would be greatly appreciated.

Thanks

 

[boblarson]

You could use a combo of DLookup and DMax.

=DLookUp("[Amount]","Sales","[Rep] ='" & [Rep] & "' And [SaleDate]=#" & DMax("[SaleDate]", "Sales", "[Rep]='" & [Rep] & "'"))

Or perhaps just build a query to pull the Last amount for a rep and then use that as the table for the DLookup instead of "Sales."

 

[Edgarr8]

I would have nver thought of breaking it up and first bringing up the last or MAX then looking where it matched the Rep...that was great.

It didnt work but i know which way to head now...thanks so much.

Once i get it i will post it back..