Date conversions...

[Mark]

I'm creating a new database program, but I need to import
some data from an old program (both Acc2000). Howerver,
some of the dates stored in the old database are stored as
text, not date/time, and when I convert them, I get really
ugly dates, like 10/02/1931 instead of 10/31/2002. Is
there any way to convert the dates properly, or am I going
to have to change all these dates manually after I import
them?

[Bish]

Microsoft stores dates as a number. Each date is the
number of days before of after the base of all dates which
is the 31st December 1899 which equals 1. Today (7th July
2003) is 37809. If you have data in a field that is not
properly formatted as Date, Microsoft products cannot
convert them to the number value.

If you pump everything to Excel and then format the
appropriate columns to date you might get away with then
re-importing to Access with some success otherwise try to
organise them in some way which places all of the badly
entered date values together so that you have to only
review those records to update manually and not all of
your data.

Bish

>-----Original Message-----
>I'm creating a new database program, but I need to import
>some data from an old program (both Acc2000). Howerver,
>some of the dates stored in the old database are stored

as

>text, not date/time, and when I convert them, I get

really

>ugly dates, like 10/02/1931 instead of 10/31/2002. Is
>there any way to convert the dates properly, or am I

going

>to have to change all these dates manually after I import
>them?
>.
>

[Van T. Dinh]

In which way they are ugly???

02 October 1931 and
31 October 2002

look to be perfectly valid to me.

If you have 2-digit year, Windows makes some assumptions about the century
and you have to adjust accordingly.


--
HTH
Van T. Dinh
MVP (Access)



"Mark" <mjkoopman@yahoocom> wrote in message
news:77f101c3449e$21cab480$a401280a@phx.gbl...

> I'm creating a new database program, but I need to import
> some data from an old program (both Acc2000). Howerver,
> some of the dates stored in the old database are stored as
> text, not date/time, and when I convert them, I get really
> ugly dates, like 10/02/1931 instead of 10/31/2002. Is
> there any way to convert the dates properly, or am I going
> to have to change all these dates manually after I import
> them?

[Milan]

Yes,

You can convert any type of date into a valid date. You
have to know a few string manipulation functions such as
Mid(), Left(), Right etc and you can use the date
conversion function cvdate().

For example to convert "021231" (a string) into a valid
date you would extract using mid() function each of the
three pairs of digits "02", "12" and "31" and them compile
them into a proper date like this cvdate("12" & "/" & "02"
& "/" & "31") which will give you #12/31/02# or Dec 31,
2002.

This works perfectly fine on a machine that is using
mm/dd/yy as the date format.

M.

>-----Original Message-----
>I'm creating a new database program, but I need to import
>some data from an old program (both Acc2000). Howerver,
>some of the dates stored in the old database are stored

as

>text, not date/time, and when I convert them, I get

really

>ugly dates, like 10/02/1931 instead of 10/31/2002. Is
>there any way to convert the dates properly, or am I

going

>to have to change all these dates manually after I import
>them?
>.
>