Once again, how replace dates?

[twgonder]

This has driven me nuts before. I've spent about an hour trying to just add a year to certain dates in both the table and a query.



This error makes no sense to me, it should be simple enough. This is what I see after pressing Replace.

[pbaldy]

I'm not sure you can do it that way, though I've never tried. I'd use the DateAdd() function to add a year to any date in 2023 with an update query.

[June7]

It works for me on a table. I suspect you have a non-editable query.

[twgonder]

@ June7 #3 I can't seem to get it to work, same error over and over no matter what I try in the table or query. The query I just created from scratch, so nothing weird.
Access 2021

[June7]

Guess you will have to provide your db for analysis.

[ArviLaanemets]

Dates in query result being aligned to right indicates, that those are not datestrings, but real dates. This means, that you are trying to replace in number 45199 (what is the numeric value of date 30/09/2023) the part of this number '2023' with '2024'! I'm not sure this will work at all!

[June7]

As stated in post #3, works for me.

[Welshgasman]

Works for me on a table as well.
However when I do it, it shows the name of the field, not Current Field?

[ArviLaanemets]

As stated in post #3, works for me.

Maybe! I don't see any reason even to try it out! Why doesn't OP simply run an update guery for table, and replace dates from year 2023 with ones from year 2024 (the same way pbaldy adviced in post#2)? Following the picture in post#1, all dates from year 2023 must be replaced anyway.

[June7]

I have no idea why they choose this approach, just saying it worked in my test.

[isladogs]

I suspect you have a non-editable query.

If its a read only query, you cannot select Replace.

Whilst I would also use an update query, the approach shown in #1 should work
Just a guess - perhaps the date field has a validation rule that blocks future dates?

[CJ_London]

post deleted - wrong search term (too early in the morning, need a coffee)

[twgonder]

@ various I think I figured out the problem.
It seems if there is formatting in a date in the table definition, Access can't handle that.
I like to display dates in the ISO format:


Removing the format, making the change in the table with Replace worked.
Then I added the formatting back. Interesting.

[Minty]

Not being picky, but... The example you posted up in #1 didn't have that format, so we wouldn't have tried that or suspected a formatting issue as it appeared unformatted?

[twgonder]

@Minty, #14 As I said in the OP, it was an hour of trying different things in the table and queries (trying different formats in properties). I'm sure you didn't want a screen shot of every test. Turns out whatever format it is or whether it's done in a table, or query, it just doesn't work with special formatting in the table. As best as I've been able to figure out. If you null out the format, I wonder what Acces is using for the format, since the date isn't showing as a double.