Access 2016 DateAdd with VB Update SQL Statement

Here is the correct syntax:

tmpSQL = "UPDATE tblMaintenanceLog SET tblMaintenanceLog.Archived = 'Yes'" _
            & " WHERE (tblMaintenanceLog.Maint_Date > DateAdd(""d"", " & tmpTimeLogs & ",  Maint_Date));"

Select allOpen in new window

Still getting syntax error 3075.

I am doing days and not a date so in this case my tmpTimeLogs = 5

But it is saying syntax error in query expression
(tblMainenanceLog.Maint_Date > DateAdd("d", 5, Maint_Date);

In the example given by Máté Farkas he is missing a closing )

Archived is a text field and not boolean?

Try to use single quotes:

... DateAdd('d', 5, Maint_Date)

Select allOpen in new window

try

tmpSQL = "UPDATE tblMaintenanceLog SET tblMaintenanceLog.Archived = 'Yes'" _
       & " WHERE tblMaintenanceLog.Maint_Date > DateAdd('d', " & tmpTimeLogs & ", Maint_Date)"

Select allOpen in new window

or

tmpSQL = "UPDATE tblMaintenanceLog SET tblMaintenanceLog.Archived = ""Yes""" _
       & " WHERE tblMaintenanceLog.Maint_Date > DateAdd(""d"", " & tmpTimeLogs & ", Maint_Date)"

Select allOpen in new window

I'd try

tmpSQL = "UPDATE tblMaintenanceLog  " & vbCrLf & _
         "SET tblMaintenanceLog.Archived = 'Yes' " & vbCrLf & _
         "WHERE (tblMaintenanceLog.Maint_Date > DateAdd(""""d"""", 5,  Maint_Date));"

Select allOpen in new window

by the way what's the datatype of tblMaintenanceLog.Archived?

Archived is Short Text.  It is set as Yes or No.
I don't get any errors but the update query is not updating the records.

put a break point and copy/paste the value of tmpSQL here...
so we can easily find out the issue that way...

It kind of makes sense as the criteria is

Maint_Date  > Maint_Date + 5 days

Select allOpen in new window

which can never happen.

I'd forget about any VBA right now and simply build a normal query, once that is functional, convert it to VBA.

It does but that 5 is not always going to be a 5.  That value is drawn from tmpTimeLogs.  It will however always be in days.

UPDATE tblMaintenanceLog  
SET tblMaintenanceLog.Archived = 'Yes'
WHERE (tblMaintenanceLog.Maint_Date > DateAdd(""d"", 5,  Maint_Date));

This is what it is saying

UPDATE tblMaintenanceLog SET tblMaintenanceLog.Archived = 'Yes' WHERE tblMaintenanceLog.Maint_Date > DateAdd('d', '5', Maint_Date)

This is what I get with HainKurt code.  It runs but does not update the Archived field

As long as tmpTimeLogs is positive, the criteria will never be met.  The date can never be bigger than a greater date (based on itself).

UPDATE tblMaintenanceLog
   SET tblMaintenanceLog.Archived = 'Yes'
 WHERE tblMaintenanceLog.Maint_Date > DateAdd('d', '5', Maint_Date) 

Select allOpen in new window

probably you need to change second Maint_Date with Now or another date column in your table
check your table and logic...

So when I read that out loud I get it.  I will need to do some updating of the code.  But it is doable.
Thanks all for the help

maybe this is what you want to do

UPDATE tblMaintenanceLog
   SET Archived = 'Yes'
 WHERE Maint_Date < DateAdd('d', -5, NOW)
   AND Archived <> 'Yes' 

Select allOpen in new window

meaning, if maintenance is before 5 days prior to today, mark it archived...

So this how I envisioned it to work.
The programs I work on need to archive logs based on who their inspector is.  22 programs have different inspectors.
Some are 18 months, some our 36 months and some are 12 months for log archiving.

In their portion of the app they enter the time frame.  They choose months, days, weeks or years.  Regardless of what they choose the code converts it all to days.  Makes it way easier with the inspectors.

So if tmpTimeLog = 5 days then logs older then 5 days need to be archived,  But that time frame needs to be based on the date they were entered. Not on todays date.  Now I can do an update query that does this, but I don't know if it is possible to add a variable from a module into it.  Like tmpTimeLogs which pulls its data from a module called Time_Logs.

Make sense?

HainKurt,

I used your SQL statement instead and just do the openquery mthod in the code.  Works like a charm now.

Thank you all who helped.