wrong date format

[Beth]

As an untrained user of Access, I have foolishly created a
simple database using the date format dd/mm/yyyy (text).
I now need to extract records within certain timeframes
and can't see how I can do this without retyping the dates
in number format for all 10,000 records. I would really
appreciate some help.

Thanks
Beth

 

[Allen Browne]

You have a Text field containing dates, and you want to convert that to a
real date/time field. You can use an Update query to populate the date for
the 10000 rows.

1. Open your table in design view, and add a new field of type Date/Time.
Leave the text field in place. Save.

2. Create a query into this table, and drag the new date/time field into the
grid. In the Criteria row under this field, enter:
Is Not Null

3. Change the query to an Update query: Update on Query menu.
Access adds an Update row to the grid.

4. In the Update row beneath the new date/time field, enter:
DateSerial(Right([td],4), Mid([td], 3,2), Left([td],2))
replacing the [td] with the name of your text date field.

5. Run the query.

6. After confirming the date field is correctly populated, open the table in
design view, and delete the text field that you no longer need.

 

[Tim Ferguson]

I have foolishly created a
simple database using the date format dd/mm/yyyy (text).
I now need to extract records within certain timeframes
and can't see how I can do this without retyping the dates
in number format for all 10,000 records.

It's not hard to do this in a query: in the query designer use a new column
and put in something like

ActualDateValue: CDate([TextDateField])

and then put in the criteria underneath in the usual way. If this chokes
because of your regional settings, then you could parse it out the long
way:

AVD: DateSerial(Mid([TDF],7,4), Mid([TDF],4,2), Mid([TDF],1,2))

You might like to restructure the table by adding a new date field,
populating it with an update query like one of those above, then removing
the old text field. Remember to back everything up first though!!

Hope that helps


Tim F

 

[Beth]

Allen

Thank you for this. Unfortunately I don't think I have the
expertise to do this - I am out of my depth at stage 3.!
I probably shouldn't have tried to create this database
until I know what I was doing.

Thanks anyway.
Beth

-----Original Message-----
You have a Text field containing dates, and you want to convert that to a
real date/time field. You can use an Update query to populate the date for
the 10000 rows.

1. Open your table in design view, and add a new field of type Date/Time.
Leave the text field in place. Save.

2. Create a query into this table, and drag the new date/time field into the
grid. In the Criteria row under this field, enter:
Is Not Null

3. Change the query to an Update query: Update on Query menu.
Access adds an Update row to the grid.

4. In the Update row beneath the new date/time field, enter:
DateSerial(Right([td],4), Mid([td], 3,2), Left ([td],2))
replacing the [td] with the name of your text date field.

5. Run the query.

6. After confirming the date field is correctly populated, open the table in
design view, and delete the text field that you no longer need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

As an untrained user of Access, I have foolishly created a
simple database using the date format dd/mm/yyyy (text).
I now need to extract records within certain timeframes
and can't see how I can do this without retyping the dates
in number format for all 10,000 records. I would really
appreciate some help.

Thanks
Beth

.