Update Query - Add one year to the date 2

[bxgti4x4]

I have a table which lists events that occur on the same date each year and a form which extracts data from this table and lists the events in date order. Unfortunately, month end is the 26th of the month, so some of the events at the end of each month are included in those of the next month. Consequently, some events in December of one year are considered as taking place in January of the following year. Were this not the case, the year would not matter.

Because of this, I have to include the year in the date and, in the past have laboriously gone through the table changing the date manually.

In order to save time, I was wondering if there is a way I can use an update query to add one year to the existing dates.

Any help would be much appreciated

Best Regards
John

 

[lupins46]

Drop this into the sql view of a new query.
Backup your db before testing.

Update mytable set mydatefield =
dateadd("yyyy",1,mydatefield)

 

[SJMcAbney]

For convenience, when 'running' the query, turn off the Warnings. After the query has 'run' reactivate the warnings.

i.e

I typically use a sub like this to save me the trouble and repeating the code for every action query I run.

Public Sub RunQuery()
With DoCmd
.SetWarnings False
.OpenQuery MyQuery
.SetWarnings True
End With
End Sub

Stewart

 

[SJMcAbney]

Error: (forgot to pass parameter)


Public Sub RunQuery(ByVal MyQuery As String)
With DoCmd
.SetWarnings False
.OpenQuery MyQuery
.SetWarnings True
End With
End Sub

Stewart

 

[bxgti4x4]

Thanks to both of you for your very helpful advice. DateAdd works fine and will save me a lot of time. The hint about SetWarnings will be very useful when I run the query from a command button.

Best Regards
John