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.
Need to put in year to "Find" a date?
-
- 4StarLounger
- Posts: 582
- Joined: 06 May 2012, 20:05
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need to put in year to "Find" a date?
What exactly do you mean by "I can't replace it now"?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 582
- Joined: 06 May 2012, 20:05
Re: Need to put in year to "Find" a date?
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.)
Microsoft Office finished searching the records. The Search Item was not found. (However, it DID find the first of 7 requested records.)
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need to put in year to "Find" a date?
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?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 582
- Joined: 06 May 2012, 20:05
Re: Need to put in year to "Find" a date?
I am using the Find tab to Find and the Replace tab to Replace.
I am managing to Find, but not Replace.
I am managing to Find, but not Replace.
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need to put in year to "Find" a date?
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.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 582
- Joined: 06 May 2012, 20:05
Re: Need to put in year to "Find" a date?
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.
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.
-
- 4StarLounger
- Posts: 582
- Joined: 06 May 2012, 20:05
Re: Need to put in year to "Find" a date?
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.
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.
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need to put in year to "Find" a date?
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...
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...
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 582
- Joined: 06 May 2012, 20:05
Re: Need to put in year to "Find" a date?
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.
Thank you, again, for your trouble.