Create SQL statement

[Melinda]

Hello,
Access 97 problem here. I'm trying to create an SQL
statement to pick out the most recent date/time for each
JobCode in my table and then delete all of the records in
my table that don't have that date/time. Here is my code:

Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM audWorkOrder2 WHERE Max
(audWorkOrder2.audDate)"
Set qdf = dbs.CreateQueryDef("MostRecent", strSQL)
Delete * FROM audWorkOrder2 WHERE
audWorkOrder2.audDate<>MostRecent

The problem is, I'm not sure how to tell VB to group my
Max audDate by JobCode. Also, the Delete * gives me a
compile error. Any ideas?
Thanks!
Melinda

 

[Gerald Stanley]

Give this a try
DoCmd.RunSQL "DELETE FROM audWorkOrder2 T1 WHERE audDate <
(SELECT Max(audDate) FROM audWorkOrder2 T2 WHERE T1.jobCode
= T2.jobCode)"

You will need to change jobCode if that is not the name of
the column in the table.

Hope This Helps
Gerald Stanley MCSD

 

[Melinda]

Thank you! That worked perfectly! And it's much classier
than my code!
Melinda