Update query (1 Viewer)

[prasadgov]

We receive a csv file daily in which almost most fields are static except for few fields and the date.
Since one daily file was missed , someone erroneously updated the Access table with the csv file of a previous date.

The date time field is like 2024-02-25 10:18:38 . Is there a way to update just the day from 25 to 27, leaving the rest of the values intact?
I need the 2024-02-27 10:18:38 leaving the yyyy-mm part and the hh:mm:ss intact? Since the data is several thousand rows, I think a update query would be faster.

TIA

 

[Gasman]

Use DateDiff()

 

[plog]

Yes, use the DateAdd function to calculate the new value based on the old.

MS Access: DateAdd Function

This MSAccess tutorial explains how to use the Access DateAdd function with syntax and examples. The Microsoft Access DateAdd function returns a date after which a certain time/date interval has been added.

www.techonthenet.com

 

[Gasman]

Yes, use the DateAdd function to calculate the new value based on the old.

MS Access: DateAdd Function

This MSAccess tutorial explains how to use the Access DateAdd function with syntax and examples. The Microsoft Access DateAdd function returns a date after which a certain time/date interval has been added.

www.techonthenet.com

Oops sorry, I meant DateAdd().
Flying home, so busy packing.

My apologies for the wrong function.

 

[prasadgov]

I tried an update query on my field, insertdatetime as

update to ateAdd("dd",2,[insertdatetime] and criteria as [insertdatetime]
but it updated it to 2024-02-27 and left out the time part

 

[Gasman]

Allways start with a Select to ensure you have what you want, then convert to an update query.
Hopefully you backed it up before running the query?
Restore the table and carry out Select first.

Just tested in immediate window

? dateadd("d",2,now())
29/02/2024 11:55:50

 

[plog]

1. Always backup your data before running an action query (INSERT, UPDATE, DELETE).

2. Always start with SELECT query to make sure you get the right records and the right update value.

SELECT YourTableNameHere.YourDateFieldHere, DateAdd("d",2,[YourDateFieldHere]) AS NewValue, DateValue([YourDateFieldHere]) AS JustTheDate
FROM YourTableNameHere
WHERE (((DateValue([YourDateFieldHere]))="2/25/2024"));

3. When the SELECT returns the right records and generates the correct new value, then convert it to an UPDATE query:

UPDATE YourTableNameHere SET YourTableNameHere.YourDateFieldHere = DateAdd("d",2,[YourDateFieldHere])
WHERE (((DateValue([YourDateFieldHere]))="2/25/2024"));

The above codes worked on a test table I set up. The final field included the right date and time values.

 

[prasadgov]

Allways start with a Select to ensure you have what you want, then convert to an update query.
Hopefully you backed it up before running the query?
Restore the table and carry out Select first.

Just tested in immediate window

? dateadd("d",2,now())
29/02/2024 11:55:50

Rather than now(), is it possible to retain the time part and just change the day from 27 to 29?
If the value is 2024-02-27 10:18:38, i need it as 2024-02-29 10:18:38 and if it 2024-02-27 10:19:40 it need it as 2024-02-29 10:19:40

 

[Gasman]

I just used now() as I did not have many fields with a time element?
Would not of thought it would make difference.

Works just as well, so no clue as to what you have done?

SELECT tblTransfer.TransferID, tblTransfer.AmendedDate, DateAdd("d",2,[AmendedDate]) AS Expr1
FROM tblTransfer
WHERE (((tblTransfer.AmendedDate) Is Not Null));

 

[Minty]

Dateadd does exactly what it says on the tin. It adds two days to the existing DateTime value.

?Now
27/02/2024 17:16:05

?Dateadd("d",2,#27/02/2024 17:16:05#)
29/02/2024 17:16:05

 

[ebs17]

Take @plog's query with a slight modification (DateValue produces a date value)

UPDATE YourTableNameHere
SET YourDateFieldHere = DateAdd("d", 2, YourDateFieldHere)
WHERE DateValue(YourDateFieldHere) = #2/25/2024#

or the criterion with possible index use

...
WHERE YourDateFieldHere >= #2/25/2024#
  AND YourDateFieldHere < #2/26/2024#

 

[prasadgov]

I just used now() as I did not have many fields with a time element?
Would not of thought it would make difference.

Works just as well, so no clue as to what you have done?

View attachment 112806

SELECT tblTransfer.TransferID, tblTransfer.AmendedDate, DateAdd("d",2,[AmendedDate]) AS Expr1
FROM tblTransfer
WHERE (((tblTransfer.AmendedDate) Is Not Null));

Thanks much. It worked but for times like 2024-02-27 20:45:01 it updated to 2024-02-29 8:45:01 PM. Is it about settings?

 

[Gasman]

Do not worry about that. That is just a display format. The time has not changed.