DLookup

[Rusty Rutherford]

I'm trying to have DLookup return the second row in a table in report. I've
tried the following but it doesn't work.

DLookUp("[Name]","Table","[AutoNumber] =" & ((SELECT Min(AutoNumber) FROM
Table)+1))

Any suggestions?

Thanks!

 

[Douglas J. Steele]

DLookUp("[Name]","Table","[AutoNumber] =" & _
Nz(DMin("AutoNumber", "Table"),0)+1)

 

[George Nicholson]

Try:
"[AutoNumber]=" & (Dmin("AutoNumber","Table")+1)

Note that Autonumbers are not always incremental: any cancelled "add record"
action or failed append will leave gaps in the sequence, so "+1" is not
reliable.

If you are using code, you would be better off using DMin to get the
smallest value and then use DMin again to get the smallest value that isn't
the first value you found.

OR, create a saved query using TOP & then use DLast() or DMax():

SELECT DISTINCT TOP 2 AutoNumber
FROM Table
ORDER BY AutoNumber ASC

The query will return the first 2 unique AutoNumbers.

To get the last record:

DLast("AutoNumber", "MyQuery")

(Or use DMax)

HTH

 

[rrutherford]

worked like a charm! thanks

DLookUp("[Name]","Table","[AutoNumber] =" & _
Nz(DMin("AutoNumber", "Table"),0)+1)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to have DLookup return the second row in a table in report.
I've tried the following but it doesn't work.

DLookUp("[Name]","Table","[AutoNumber] =" & ((SELECT Min(AutoNumber) FROM
Table)+1))

Any suggestions?

Thanks!