Update query for 12/30/1899 dates

[P5C768]

I am trying to run an update query to get rid of blank dates that are showing up as 12/30/1899, but when you click on the value in the table, it switches to 12:00:00 am.



Why would my update query miss some values?

[RuralGuy]

The field is not blank. It contains 0.0 which equates to Midnight on 12/30/1899. Here's a link that might help in understanding the DateTime field a little better.

[P5C768]

Sorry, I should clarify. I hvae an update query that should replace 12/30/1899 or 0.0 or 12:00:00 am with " ". The first time I ran it, it replaced ~400 values.

However, when I go into the table, some records still display 12/30/1899. When I try to rerun my update query, it displays a message saying that I am about to over write 0 records.

I'm wondering how to get my query to replace all records in a field that have 12/30/1899 or 0.0 or 12:00:00 am with " ".

[RuralGuy]

A DateTime field is numeric and will not accept a string such as " ".

[RuralGuy]

It might allow you to set it back to a Null however, but I'm not sure.

[P5C768]

It does allo me to use Null as the Update to field, but I think the problem is that my query isn't finding all instances of 0.00 dates. I have tried using or to search for 0.00, 0 and 12/31/1899, but it still says all fields have been fixed. Any reason the query would miss some values? Could they be in a 0.000000 format or something similar?

[RuralGuy]

Post the SQL for your Update query so we can look at it.

[P5C768]

I think this issue is resolved, but I may have another question. The problem isn't actually with dates that are 0.0. I imported the data from excel, and some of the dates are text fields (i.e. 12312007). Is there a way to convert these to dates in access? I am thinking I will need to fix them in excel and reimport.

[RuralGuy]

There are certainly way to convert a string date to a DateTime value but the import should give you some problems because of the different field types. I would tend to fix it in Excel unless this will be happening often. In that case, import everything as text and fix it in Access.