Access 2003 date conversion from older nonaccess db

[Guest]

I just inherited a large database that has several date fields which came
across in the conversion to Access from an older wierd database program as
straight text:

MMDDYYYY
05021991

And I need to convert all these straight text dates to real dates.

How would I do this?

Thanks
Frank in Spokane
Home of Bloomsday

 

[Allen Browne]

1. Add a date/time field to your table.

2. Create a query into the table.


3. Change it to an Update query (Update on query menu.)
Access adds an Update row to the grid.

4. Drag the new date field into the grid.

5. In the Update row under this field, enter:
DateSerial(Right([d],4), Left([d],2), Mid([d],3,2))
replacing d with the name of your field.

6. Run the query.

 

[Paul Overway]

Here's one way...

CDate(Left([field],2) & "/" & Mid([field],3,2) & "/" & Right([field],4))

Use this expression in an update query.

 

[Douglas J. Steele]

I'd go with Allen's recommendation to use DateSerial, not CDate. CDate will
convert the dates incorrectly if the user's Short Date format has been set
to dd/mm/yyyy.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Here's one way...

CDate(Left([field],2) & "/" & Mid([field],3,2) & "/" & Right([field],4))

Use this expression in an update query.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

I just inherited a large database that has several date fields which came
across in the conversion to Access from an older wierd database program
as
straight text:

MMDDYYYY
05021991

And I need to convert all these straight text dates to real dates.

How would I do this?

Thanks
Frank in Spokane
Home of Bloomsday

 

[Paul Overway]

True...given the ambiguous nature of the example. Although, Frank does say
MMDDYYYY, and I'd assume his Short Date is set the same way...but perhaps
not.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

I'd go with Allen's recommendation to use DateSerial, not CDate. CDate
will convert the dates incorrectly if the user's Short Date format has
been set to dd/mm/yyyy.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Here's one way...

CDate(Left([field],2) & "/" & Mid([field],3,2) & "/" & Right([field],4))

Use this expression in an update query.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com

I just inherited a large database that has several date fields which came
across in the conversion to Access from an older wierd database program
as
straight text:

MMDDYYYY
05021991

And I need to convert all these straight text dates to real dates.

How would I do this?

Thanks
Frank in Spokane
Home of Bloomsday

 

[Guest]

That worked great Allen - thanks.
I understand the logic of it all and have added the routine to my toolbox.
Have a great day Down Under!

1. Add a date/time field to your table.

2. Create a query into the table.


3. Change it to an Update query (Update on query menu.)
Access adds an Update row to the grid.

4. Drag the new date field into the grid.

5. In the Update row under this field, enter:
DateSerial(Right([d],4), Left([d],2), Mid([d],3,2))
replacing d with the name of your field.

6. Run the query.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

I just inherited a large database that has several date fields which came
across in the conversion to Access from an older wierd database program as
straight text:

MMDDYYYY
05021991

And I need to convert all these straight text dates to real dates.

How would I do this?

Thanks
Frank in Spokane
Home of Bloomsday

 

[Tim Ferguson]

True...given the ambiguous nature of the example. Although, Frank
does say MMDDYYYY, and I'd assume his Short Date is set the same
way...but perhaps not.

Until the German temp sets her control panel to what she has always used...


Tim F

 

[Guest]

I hadn't actually set my short date format to anything - but the conversion
routine worked fine. Thanks to All !

Frank in Spokane