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.
It works for me on a table. I suspect you have a non-editable query.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
@ 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
Guess you will have to provide your db for analysis.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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!
As stated in post #3, works for me.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Works for me on a table as well.
However when I do it, it shows the name of the field, not Current Field?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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.
I have no idea why they choose this approach, just saying it worked in my test.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
post deleted - wrong search term (too early in the morning, need a coffee)
Last edited by CJ_London; 03-06-2024 at 04:24 AM. Reason: wrong search term
@ 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.
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?
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
@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.