New question on changing dates

[twgonder]

I had problems before using dd/mm/yyyy formats in various places, and there's a post on that.

Now, I'm trying to change dates via a query, but I can't figure out what the problem is.

Here's the image:


I can find the date in the Find What box, and you can see it's highlighted,
but when I press Replace, the message says it can't find what it just found!

Does this make any sense?
I've tried with Search Fields as Formatted and that didn't work either.
Part of the confusion, I can't figure out whether it wants dd/mm or mm/dd which should be based on Regional settings.

[Gicu]

What happens if you change the Match to Any Part of Field?

Cheers,

[twgonder]

@ Gicu, I tried lots of things, nothing reliably worked.
When I can get it to work, the query goes Not Responding (against the BE table)
and then I get this kind of message:



which puts in doubt a split database working reliably when using queries.
The query loads, but updating fails.

[Minty]

Have you tried replacing with

1900.02.01 00:00:00

?

[twgonder]

@ Minty post #4it
There are two problems I've come up against when working with this large BE table.
1. Find works, and replace works, but Replace All fails to find what an individual Replace just found.
2. If I use the format, it replaces in some records and then the Replace operation with the query fails, with the error message shown in post #3

The only thing that seemed to work was an update query, but that seemed again confusing as it used the regional format and not the USA format, contrary to what many claim is the norm in SQL.

It's not all super important in that I'm going to redo the large BE table.
But it would be nice to know for the future, what's the problem with ribbon Replace against tables and queries.

[Minty]

My understanding of how it works:
In the query editor it will accept and deal with your locale format, you can prove this by entering a date in your local format and switching to SQL view, it will have changed it to what it understands.
In VBA it expects US format, however it accepts dates in other formats if they can't be deciphered as an American format e.g. 28/11/2023 is unambiguously the 28-November so Access helpfully(? no not really) accepts it...

The best route is to use the completely unambiguous yyyy-mm-dd format in VBA and the query window. As far as I am aware it always works wherever you are, and is the expected format in SQL server.

As for your issue, I suspect it's more to do with the Find/Replace dialog than anything else.
It's really designed around a text find and replace function and as such is not 100% reliable with formatted data in my experience.
Try searching for Nulls for instance - it invariably needs you to put in "" instead, which isn't valid data in numeric fields but magically it finds them.