Last Invoice Amount - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Last Invoice Amount

Walter Hamilton

4 years ago

I've been asked to expose the last amount invoiced on our Active Programs Dashboard. I'm using DSum to calculate the total amount invoiced for each ChargeCode_ID but I'm not sure how to reference the tbl_Invoices to find the last amount invoiced for a given ChargeCode_ID. I'm on a time crunch for this feature so I haven't started watching videos yet. If this is simple can somebody assist with the syntax. If not, please point me to the correct video. Thanks!

	Scott Axton @Reply 4 years ago
Just use DSum's little brother DMax on the invoice number to find the last one. Might need to do a DLookup in conjunction with that DMax to get the amount as well.

	Alex Hedley @Reply 4 years ago
`SELECT TOP 1 [Amount] FROM tbl_Invoices WHERE ChargeCode_ID=1 ORDER BY [DateField] DESC`

	Walter Hamilton OP @Reply 4 years ago
Scott - I have the following which is returning the highest Invoice No: `=DMax("[Invoice No]","[tbl_Invoices]","[ChargeCode_ID]=" & [ChargeCode_ID])` How would I add DLookup to get the [Amount] from that Invoice No?

	Scott Axton @Reply 4 years ago
Walter - Just set your DMax result to a variable then use that result for the DLookup. I would put it in the on current event for your form. `Dim xID as Long xID = DMax("[Invoice No]","[tbl_Invoices]","[ChargeCode_ID]=" & [ChargeCode_ID]) YourTextbox = DLookup("Amount", "tbl_Invoices", "[Invoice no]=" & xID)` Make sure I got your fields right.

	Walter Hamilton OP @Reply 4 years ago
Scott, Thank you. I was able to get everything working.

	Richard Rost @Reply 4 years ago
Walter, when you get a chance, watch this: VLOOKUP in Access. Very similar concepts.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View