deleting all records below the maximum value

[Paul James]

I'm trying to create a SQL statement that will delete all records in a table
where the values in a particular field are less than the maximum value. So
after this statement is executed, the only remaining record in the table
will be the one with the largest value in that field.

My table is named tblTMAAgentID, and the field is named AgentID.

I tried using the following statement:

currentdb.Execute "DELETE * FROM tblTMAAgentID WHERE AgentID < Max(AgentID)"

but I get an error message saying that I can't have the Max(AgentID)
aggregate function in a WHERE clause.

Can someone please tell me how to write the SQL statement so I can
accomplish my objective?

Thanks in advance,

Paul

 

[John Spencer (MVP)]

Try using either a subquery in the where clause or the DMax function;

DELETE * FROM tblMAAgentID WHERE AgentID < DMax("AgentID","tblMAAgentID")

or

DELETE *
FROM tblMAAgentID
WHERE AgentID <
(SELECT MAX(A.AgentID)
FROM tblMAAgentID As A)

 

[Paul James]

Thanks for the suggestions, John. The statement with the subquery works
just fine, so I'll be able to use it. However, I'm also trying to learn as
much as I can from the responses that you wonderful folks post in these
newsgroups, and when I tried using the statement with the DMax function as

currentdb.execute "DELETE * FROM tblTMAAgentID WHERE AgentID <
DMax("AgentID","tblTMAAgentID")"

I get the message "Expected: end of statement.

I know that the DMax clause is ok because I tested it in the Immediate
Window, and it correctly displays the Maximum value in the AgentID field.
But evidently there's a problem elsewhere in the syntax. Any idea where it
might be?

Thanks again for your help with this.

Paul

 

[John Spencer (MVP)]

Sure, the extra quotes are messing you up. VBA thinks that the string ended
when it gets a matching quote. To handle that you need to double up the quotes.
Two quotes immediately adjacent will get translated into one quote when the
string is constructed.

currentdb.execute "DELETE * FROM tblTMAAgentID WHERE AgentID < DMax(""AgentID"",""tblTMAAgentID"")"

In this case, you could also use -
CurrentDb.Execute "DELETE * FROM tblMAAgentID WHERE AgentID <" & DMax("AgentID","TblMAAgentID")

That assumes that AgentID is a numeric value.

 

[Paul James]

You're right - they both work. Thanks for illuminating this aspect of VBA
syntax for me. I never realized that it thinks the string ends when you
reach a second single quote, and how to handle that.

Thanks so much for this.

Paul