Okay a number of things - a) that code is running the same query twice. b) The currentdb.execute line is missing.
Try this code complete; and we'll see what is happening
Rich (BB code):Dim dLastRun As Date Dim dCurrRun As Date Dim sSql As String dLastRun = Nz(DLookup("propvalue", "zzz_tblSystem", "Property = 'LastRunDate'"), #1/1/2020#) ' This will make sure it runs the first time around Debug.Print dLastRun Debug.Print Now() - dLastRun If Now() - dLastRun >= 7 Then ' We need to run as it's been over 7 days ' 'DoCmd.SetWarnings False DoCmd.OpenQuery "Unallocate_1", acViewNormal, acEdit DoCmd.OpenQuery "Unallocate_2", acViewNormal, acEdit ' 'DoCmd.SetWarnings True ' This will now update the last run date to the next monday at 9:30. This will prevent time creep If Weekday(Date) = 2 Then ' Its Monday dCurrRun = Date + (9.5 / 24) Else dCurrRun = (Date - Weekday(Date - 3) + (9.5 / 24)) End If sSql = "UPDATE zzz_tblSystem SET LastRunDate = #" & Format(dCurrRun, "yyyy/mm/dd hh:nn") & "#" CurrentDb.Execute sSql ' This will run the sql created above. End If msgbox "The last run time was " & dLastRun & " , The new last run time is now set to " & dCurrRun
RUNTIME ERROR 2471 see pic
i changed the first line to this:
dLastRun = Nz(DLookup("LastRunDate", "zzz_tblSystem"), #1/1/2020#) ' This will make sure it runs the first time around
and the code runs completly and if updates the date in the table, however the update query come back with 0 records but when run manually i have 45
Last edited: