Changing Dates in previous Records

L

Linda

Hi All,

The database has a date field that used to be set-up with the =now()
formula, which produces a date and time. The field has been changed to
=(Date()). However, the previously entered records still have the time
attached. This causes error messages when comparisons are done with this and
other date fields.

Is there a way to permanently change all previous records entered to contain
only the date portion? There are 5 individual databases with many records
that are used and manually retyping the date in each record is not a
realistic option for the users.

Any help would be greatly appreciated.

Thanks.
Linda
 
A

Allen Browne

To lose the time component and leave the date only:

1. Create a query into this table.

2. Change it to an Update query (Update on Query menu, in query design.)
Access adds an Update row to the grid.

3. Drag your date field into the grid.
Type this into the Update row beneath this field:
DateValue([Field1])
substitutingyour field name for Field1.

4. Run the query.
 
O

Ofer

Hi Linda, first Back your data

You can use an update query to update all the dates in the table to the new
format

Update TableName Set [Field Name] = Format([Field Name],"dd/mm/yyyy")

or, if you use a different format for the date
Update TableName Set [Field Name] = Format([Field Name],"mm/dd/yyyy")
 
L

Linda

This sounds like it will work...I will test this when I can today...meetings
are taking priority. :eek:( and will give you some feedback. Thanks so much.
--
Linda



Ofer said:
Hi Linda, first Back your data

You can use an update query to update all the dates in the table to the new
format

Update TableName Set [Field Name] = Format([Field Name],"dd/mm/yyyy")

or, if you use a different format for the date
Update TableName Set [Field Name] = Format([Field Name],"mm/dd/yyyy")

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


Linda said:
Hi All,

The database has a date field that used to be set-up with the =now()
formula, which produces a date and time. The field has been changed to
=(Date()). However, the previously entered records still have the time
attached. This causes error messages when comparisons are done with this and
other date fields.

Is there a way to permanently change all previous records entered to contain
only the date portion? There are 5 individual databases with many records
that are used and manually retyping the date in each record is not a
realistic option for the users.

Any help would be greatly appreciated.

Thanks.
Linda
Click to expand...
Click to expand...
 
Top