Update query question.

[TymaUK]

Hi group

I have a table with various fields one of which being a date and time field
in the format of dd/mm/yy hh:mm:ss.

My problem is that on a couple of occasions, for some unknown reason
(probably a virus) and unknowing to the database user the PC clock year
changed to 2020 (this happened on 3 separate occasions) and as you can guess
I now have a lot of records that show as 2020 (2130 records to be precise)

I want to use an update query to change all of the 2020 records back to
2006. The trouble is that because of the format of the records (i.e
20/01/20 12:12:01) and the time changing/being different I cannot use the
argument:

'Update to' #20/01/06#
'Criteria' #20/01/20#

Is there another easy way of updating all of the wrong records without
having to manually change them.

Hope I've explained myself clearly enough.

Many thanks for your help.

TK

 

[John Spencer]

Obviously, make a back up of your data before your do this, so that if
something goes wrong you can recover the data.

In the grid
Field: YourField
Update To: DateAdd("yyyy",-14,[YourField])
Criteria: >= #2020-01-01# and < #2021-01-01#

Or the equivalent SQL statement
UPDATE YourTable
SET YourField = DateAdd("yyyy",-14,[YourField])
WHERE YourField >= #2020-01-01# and YourField < #2021-01-01#

 

[TymaUK]

John

Many thanks for your reply, it worked!

Any chance of explaining how the 'DateAdd' actually worked, just for future
reference.

Thank you.

Obviously, make a back up of your data before your do this, so that if
something goes wrong you can recover the data.

In the grid
Field: YourField
Update To: DateAdd("yyyy",-14,[YourField])
Criteria: >= #2020-01-01# and < #2021-01-01#

Or the equivalent SQL statement
UPDATE YourTable
SET YourField = DateAdd("yyyy",-14,[YourField])
WHERE YourField >= #2020-01-01# and YourField < #2021-01-01#

Hi group

I have a table with various fields one of which being a date and time
field
in the format of dd/mm/yy hh:mm:ss.

My problem is that on a couple of occasions, for some unknown reason
(probably a virus) and unknowing to the database user the PC clock year
changed to 2020 (this happened on 3 separate occasions) and as you can
guess
I now have a lot of records that show as 2020 (2130 records to be precise)

I want to use an update query to change all of the 2020 records back to
2006. The trouble is that because of the format of the records (i.e
20/01/20 12:12:01) and the time changing/being different I cannot use the
argument:

'Update to' #20/01/06#
'Criteria' #20/01/20#

Is there another easy way of updating all of the wrong records without
having to manually change them.

Hope I've explained myself clearly enough.

Many thanks for your help.

TK

 

[John Spencer]

DateAdd is in the VBA help.
Switch to the VBA window (Control+G) and press F1
Type DateAdd into the Index keywords and press the search button.

That should give you a complete explanation.

John

Many thanks for your reply, it worked!

Any chance of explaining how the 'DateAdd' actually worked, just for
future
reference.

Thank you.

Obviously, make a back up of your data before your do this, so that if
something goes wrong you can recover the data.

In the grid
Field: YourField
Update To: DateAdd("yyyy",-14,[YourField])
Criteria: >= #2020-01-01# and < #2021-01-01#

Or the equivalent SQL statement
UPDATE YourTable
SET YourField = DateAdd("yyyy",-14,[YourField])
WHERE YourField >= #2020-01-01# and YourField < #2021-01-01#

Hi group

I have a table with various fields one of which being a date and time
field
in the format of dd/mm/yy hh:mm:ss.

My problem is that on a couple of occasions, for some unknown reason
(probably a virus) and unknowing to the database user the PC clock year
changed to 2020 (this happened on 3 separate occasions) and as you can
guess
I now have a lot of records that show as 2020 (2130 records to be precise)

I want to use an update query to change all of the 2020 records back to
2006. The trouble is that because of the format of the records (i.e
20/01/20 12:12:01) and the time changing/being different I cannot use the
argument:

'Update to' #20/01/06#
'Criteria' #20/01/20#

Is there another easy way of updating all of the wrong records without
having to manually change them.

Hope I've explained myself clearly enough.

Many thanks for your help.

TK