Need to put in year to "Find" a date?

[Diana van den Berg]

I have a date/time field formatted to:

ddd, d mmm yyyy

and I can "Find" it if I type e.g.

Sat, 7 Dec 2013

OR

7/12/2013

or

7/12

with the checkbox unticked for Search Field as Formatted.

However, I can't replace it now.

Minutes ago, I could replace it with

6/12

but now I can't even replace it with

6/12/2013

This is puzzling. How should it work?

Thank you for any help.

[HansV]

What exactly do you mean by "I can't replace it now"?

[Diana van den Berg]

I mean that when I try to replace it, it doesn't do anything. It says (having found the date, e.g. 7/12) and my trying to replace it with 4/12/2013 it doesn't do it and comes up with an error message:

Microsoft Office finished searching the records. The Search Item was not found. (However, it DID find the first of 7 requested records.)

[HansV]

Let's try to get this clear. Are you working in the Find tab of the Find and Replace dialog, or in the Replace tab?

[Diana van den Berg]

I am using the Find tab to Find and the Replace tab to Replace.

I am managing to Find, but not Replace.

[HansV]

I think replacing dates only works if (1) you select "Whole Field" from the Match dropdown, and (2) you tick the check box "Search Fields As Formatted", and (3) you specify the "Find What" text in the correct format.

[Diana van den Berg]

I tried that and got the following error message:

The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or a number that is larger than the Field Size setting permits.

[Diana van den Berg]

Temporarily, I changed the format to 07/12/2013 and I could Find and Replace with no problem. However, I really wanted to use the format:

ddd, d mmm yyyy

Also if one needs to type in the full format with what is immediately above, one would have to know what day of the week the new date would be.

[HansV]

I'm afraid that this is bug in Access. Replace is broken for dates, for all practical purposes.
It would be possible to create your own Replace using VBA, but that would be extra work, especially if you'd want it to be flexible...

[Diana van den Berg]

Thank you very much for the information, Hans. Yes, I do want it to be flexible - also it is unlikely that the user will want to change dates en masse. I wanted to do it for my sample records and I also wanted to know how to do it, but if it is a Microsoft bug, then I will just have to accept it.

Thank you, again, for your trouble.