Find and Replace Query

[randolphoralph]

I am using Access 2002 I am needing to create an update query that will find and replace in a field.

Table Name: All
Field Name: Date and Time

Find 1/1/1900 12:00:00 AM in the Date and Time field and replace with a blank ("").



I have tried multiple ways and can not seem to find a solution.

[randolphoralph]

Here is what I have so far and it does not work correctly

SELECT Date_and_Time, Replace(Date_and_Time,"12:00:00 AM","") AS NewDate
FROM Test;

I have tried to include spaces inside of the replacement ("") and it enters a bunch of spaces in the field after the date.

[objNoob]

UPDATE tblTest SET Date_and_Time = Null
WHERE Date_and_Time=CVDate(2);

Access stores dates as Doubles.

CVDate(2) is exactly 1/1/1900 12:00:00 AM

Use CDbl(#1/1/1900 12:00:00 AM) to convert Date / Time to Double.

CDble(x) Converts x to Double
and CVDate(x) Converts double to Date

I hope this helps!

[objNoob]

Here is what I have so far and it does not work correctly

SELECT Date_and_Time, Replace(Date_and_Time,"12:00:00 AM","") AS NewDate
FROM Test;

I have tried to include spaces inside of the replacement ("") and it enters a bunch of spaces in the field after the date.

This will not work if your column is of data type date/time, because a date/time cannot be an empty string. If your column allows NULL values, my suggested UPDATE should work given 1/1/1900 was the date you really needed to replace.

[randolphoralph]

I have tried your recommendation and then realized that the field is set to Text as the Data Type. I have tried to change the Data Type to Date/Time and save the table I receive a error message stating "Microsoft Access can not change the data type there is not enough disk space or memory"

So here is what I am needing to do for the Date and Time field.

Here are items that are currently found in this field....
1/1/1900 12:00:00 AM
2/3/2009 12:00:00 AM (various dates)
12:00:00 AM

Here is what I need to do....

replace all 1/1/1900 12:00:00 AM with a empty value
remove 12:00:00 AM from 2/3/2009 12:00:00 AM (various dates)
and replace 12:00:00 AM with a empty value

Any help would be really appreciated.