SQL statement to return highest value in autonumber field?

[nharrison]

This is more of an SQL question than a query issue.

I want to determine the max value of an autonumber field, b/c this is an easy way to access the last created record in a table. I can't use DoCmd.GoToRecord , , Last,
because I have a sort active on my recordset, and that navigates to the last person alphabetically by last name - not what I need.

I know you can use SELECT Max("Contact ID") FROM "Contacts", but how do I assign what is returned by this to a global variable I can use in VBA procedures?

 

[KenHigg]

Have you considered using the dmax() function?

 

[MSAccessRookie]

This is more of an SQL question than a query issue.

I want to determine the max value of an autonumber field, b/c this is an easy way to access the last created record in a table. I can't use DoCmd.GoToRecord , , Last,
because I have a sort active on my recordset, and that navigates to the last person alphabetically by last name - not what I need.

I know you can use SELECT Max("Contact ID") FROM "Contacts", but how do I assign what is returned by this to a global variable I can use in VBA procedures?

This type of question comes up all the time. If you search on DMax, a common method of finding the highest previous value, you should find any number of threads. Perhaps that will provide you with a good start.

Looks like Ken beat me to the post with a slighly shorter response that says the same thing. With two votes Aye, I suggest you look into DMax().

 

[nharrison]

Yep, that's what I needed. Thanks guys.

 

[KenHigg]

...Looks like Ken beat me to the post with a slighly shorter response that says the same thing. With two votes Aye, I suggest you look into DMax().

Win some, lose some