Changing Date/Time formats

[Guest]

I have been given a database to fix the was built by my supervisor, who has
virtually no experience in access.

I have a Date/Time field that was originally made as a Medium Date format,
including time. However, this format does not work with the queries and
reports I need to build. I have changed the format for all future entries to
Short Date format, no time included. However, most of the records (over 8000
to date) have the date entered in the old format.

My question is: How can I create an Update Query that will find all records
that were entered in the old format, and change them to the new format?

Thanks!

 

[Rick Brandt]

I have been given a database to fix the was built by my supervisor,
who has virtually no experience in access.

I have a Date/Time field that was originally made as a Medium Date
format, including time. However, this format does not work with the
queries and reports I need to build. I have changed the format for
all future entries to Short Date format, no time included. However,
most of the records (over 8000 to date) have the date entered in the
old format.

My question is: How can I create an Update Query that will find all
records that were entered in the old format, and change them to the
new format?

Thanks!

Format has absolutely nothing to do with what is stored. If what you actually
meant was...

"We were previously storing date and time and now we are storing all dates with
a time of midnight"

....that would be a logical statement. What exactly did you change? Becuase if
all you did was change formatting then you are still storing the dates the same
as before.

An update query that would set all of the DateTimes to a time of midnight would
be...

UPDATE TableName
SET DateField = DateValue(DateField)