CODEWITHSUNDEEP
ms-accessms-access-2010ms-access-2007
Chris
Chris

Reputation: 13

Combining DMax with Dlookup to find value from last row created

I have an access database that tracks reports numbered in this way:

NNN-[two digit site code]-YY0000

The “0000” is a sequential number assigned as the reports are issued. Examples are: NNN-SD-180001, NNN-MA-180002.

tbl_NNN stores the records. NNN_ID is the column that stores the report numbers.

The report numbers are manually assigned, so we must keep track of the last assigned number to prevent “duplicating” the sequential 0000 number. Since users do not have visibility to the entire table of assigned numbers, they do not know which is the last number assigned. To assist them, their dashboard/form has a label that displays the last number issued. The problem we have is that the label only shows numbers that include the SD site code, MA numbers are not displayed.

My solution was to add a column named “Date_Created”, which adds a =Now() time stamp whenever a new row/number is created in the table. With the timestamp, I then intended to use Dmax to display the report number that corresponds to the last row created, regardless of the report number.

I know what I need is a combination of DLookup and Dmax, but what I came up with displays

"#Error"

in the label. 

=DLookUp("NNN_ID","tbl_NNN","[Date_Created]=" & DMax("[Date_Created]","tbl_NNN"))

Am I writing this correctly?

Upvotes: 1

Views: 1669

Answers (1)

Gustav
Gustav

Reputation: 55921

You are close, but you must use single quotes (or unreadable expanded double quotes) for the embedded DMax:

=DLookUp("NNN_ID","tbl_NNN","[Date_Created] = DMax('[Date_Created]','tbl_NNN')")

Upvotes: 1

Related Questions