Update query question.

  • Thread starter Thread starter TymaUK
  • Start date Start date
T

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
 
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#
 
John

Many thanks for your reply, it worked!

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

Thank you.


John Spencer said:
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 said:
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
Click to expand...
Click to expand...
 
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.

TymaUK said:
John

Many thanks for your reply, it worked!

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

Thank you.


John Spencer said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top