CODEWITHSUNDEEP
sqlvbams-access
KDE
KDE

Reputation: 85

Trying to use a Macro/SQL statement to delete from a table (Ms Access)

Please note: Apologies for the query name, I just needed it to appear at the top of the list whilst I am trying to figure it out!

Table - Mortalityinput

Query - AAAAAAINmax

I am trying to write a macro to delete from my data table the last/max day of data e.g. (if the most recent data was 8th March and there are 5 records, I would like to delete all of those records)

My first step was creating a query to identify the max available date:

SELECT Max(Format(Mortalityinput.[Created Date],"dd/mm/yyyy")) AS Expr1 FROM Mortalityinput;

I have had to use format as the created date goes down to hh/mm/ss so only the newest record in terms of time would be picked up where as I want all records for that day.

Second step was to create a query which selected data from mortalityinput whose [Created Date] matched that of the max date. Query below:

SELECT *
FROM Mortalityinput AS inp
INNER JOIN
(SELECT Max(format(Mortalityinput.[Created Date],"yyyy/mm/dd")) AS MaxDate
FROM Mortalityinput) AS grp
ON format(inp.[Created Date],"yyyy/mm/dd") = grp.[MaxDate];

Which works fine, however I am struggling to actually delete the records, I tried using:

  Private Sub Command14_Click()
  myVar = DMax("[Created Date]", "Mortalityinput")
  MsgBox (myVar)

  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Set dbs = CurrentDb
  lcSQL = "SELECT AAAAAAINmax.[ID] from AAAAAAINmax.[ID] AS IDmax"
  Set rst = dbs.OpenRecordset(lcSQL)

     Do While Not rst.EOF
         lcSQL = "DELETE Mortalityinput.ID FROM Mortalityinput WHERE Mortalityinput.ID=" + 
         Str(rst.Fields("IDmax"))
         DoCmd.SetWarnings False
         DoCmd.RunSQL (lcSQL)
         DoCmd.SetWarnings True
         rst.MoveNext
      Loop
   End Sub

I did read about a Database.Execute method, and thought about instead of matching the ID in the table to the query I could just delete anything which has the same created date but I can't get that to work either along the lines of:

   Set dbNewInitiatives = CurrentDb
   strSQL = "DELETE FROM [Mortalityinput]" _
   & " WHERE format(AAAAAAINmax.[Created Date],"" DD/MM/YYYY"") = " & myVar
   dbNewInitiatives.Execute strSQL, dbFailOnError

Apologies if this is painful to look at for anyone, Access and SQL are not my strong point. I also tried to write/create a simple SQL statement in design mode however when I try to run it I get a message asking which table I am referring to.

Please someone put me out of my misery.

Thanks!

Upvotes: 1

Views: 413

Answers (2)

Pradeep Kumar
Pradeep Kumar

Reputation: 6969

You need to remove time part from both sides. Also, no need to mention query name AAAAAAINmax.

Try this:

myVar = Format(DMax("[Created Date]", "Mortalityinput"), "DD/MM/YYYY")

Set dbNewInitiatives = CurrentDb
strSQL = "DELETE FROM [Mortalityinput]" _
       & " WHERE Format([Created Date],'DD/MM/YYYY') = '" & myVar & "'"
dbNewInitiatives.Execute strSQL, dbFailOnError

Upvotes: 1

Francesco Giossi
Francesco Giossi

Reputation: 170

The SELECT query is wrong It should be like this

lcSQL = "SELECT [ID] AS IDmax from AAAAAAINmax "

Upvotes: 0

Related Questions