SQL Statement in Access 2007 VBA Returning max value

Posted on 2011-10-12
Medium Priority
Last Modified: 2012-05-12
I have a command button that when clicked, needs to look at a table and select the record with the highest "LotNo" number.  (which is the primary key).

Once I get that number, I'm going to look at how many pounds have been assigned to it and take some action accordingly.  But I think I'm ok on all of that, I am just fumbling in trying to get the highest number from the "tblLots" table using runcmd.sql statement.    Probably try to make it too complicated.

I've tried so many different things I wouldn't know what to post to show.... guess I'm putting the "" marks in the wrong spot.

Table name = tblLots
"LotNo", Number, Long Integer (need to return the highest number in the table)
LotDate, Date, Short (not really using this yet)
Lbs, Number, Long Integer (if the pounds are over 10,000, then I will create a new LotNo which will be the next sequential number)

Any help is appreciated.
Question by:TechGuise9
  • 2
LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 36958114

DMax("[LotNo]","tblLots")  would seem to be what you need.

LVL 75
ID: 36958131
btw ... DoCmd.RunSQL is only for executing Action queries (update, append, delete make table).


Author Closing Comment

ID: 36958662
Thanks.   Had a feeling I was making it too hard.

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question