Fixing bad data- dates

[BonnieW via AccessMonster.com]

I inherited a table. One field in this table is Date_Observed, and users
entered data directly into this (I'm not sure there was an input mask; if
there was, it was ignored). It is formatted as a Date/Time field. In it, I
have such gems as "2/1/530" and "1/1/999". I have ruled out the possibility
that my users are time-travellers, but I still have to figure out when these
things were observed. Is there a formula out there that'll help me out?

Thanks,
Bonnie

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200809/1

[Bob Quintal]

"BonnieW via AccessMonster.c om" <u33846@uwewrot e in
news:899c030bea 68b@uwe:

I inherited a table. One field in this table is Date_Observed,
and users entered data directly into this (I'm not sure there was
an input mask; if there was, it was ignored). It is formatted as
a Date/Time field. In it, I have such gems as "2/1/530" and
"1/1/999". I have ruled out the possibility that my users are
time-travellers, but I still have to figure out when these things
were observed. Is there a formula out there that'll help me out?

Thanks,
Bonnie

is there some other field that will help put the dates into a
sequence? an autonumber perhaps.
e.g.

ID
12344 1/31/2004
12345 2/1/530 <- the date is between the other two
12346 2/2/2004
--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

[MGFoster]

BonnieW via AccessMonster.c om wrote:

I inherited a table. One field in this table is Date_Observed, and users
entered data directly into this (I'm not sure there was an input mask; if
there was, it was ignored). It is formatted as a Date/Time field. In it, I
have such gems as "2/1/530" and "1/1/999". I have ruled out the possibility
that my users are time-travellers, but I still have to figure out when these
things were observed. Is there a formula out there that'll help me out?

Thanks,
Bonnie

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I usually ignore bad data & tell the data input ops to re-input (they
love it!).

You can go on the assumption that "2/1/530" is probably "2/15/30" if
your dates go to 1930. That's the most common mistake - splitting the
number into 2 different data positions (month and year, in this case).
You'll just have to reformat the date into what you think is correct &
check if that is a date:

IsDate("2/15/30") -yields True

IsDate("2/1/530") -yields True also since it is Feb 1, 530 C.E.

So, you'll have to use string parsing techniques (Len, Mid, Right, Left)
to determine if the m/d/yy positions have the correct number of numbers
& in the correct range.

The old adage is the best standard - Garbage In, Garbage Out.

--
MGFoster:::mgf0 0 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSL28mYechKq OuFEgEQJcrgCg13 H/M3gWkRkvOkOa3Ge 15InYkWIAoLei
ddl6qzdn6Mcc+f3 kKdnAnyaa
=ag4M
-----END PGP SIGNATURE-----

[BonnieW via AccessMonster.com]

There is an autonumber, as well as a date-entered, and an entered-by-whom.
So I know that the data is *before* a certain date, sure. However, the date-
entereds are from the "early years" of the database, when people may have
been entering archival data (there's perfectly valid stuff from, say, the
1950s, and in one case, the late 1800s). This table had been used for a
bunch of things (bad db design), so the autonumber "session" may be bracketed
by completely different data (herps vs plants, for instance). And, to top it
off, this is data from at least 5 years ago, and the people who had entered
it are long gone. :( We *might* still have the original data forms- but
since I'd be the one stuck finding them and interpreting them and entering
them, I'm not super-keen on that method if there's an acceptable alternate.

Bob Quintal wrote:

>I inherited a table. One field in this table is Date_Observed,
and users entered data directly into this (I'm not sure there was

[quoted text clipped - 6 lines]

>Thanks,
Bonnie

is there some other field that will help put the dates into a
sequence? an autonumber perhaps.
e.g.

ID
12344 1/31/2004
12345 2/1/530 <- the date is between the other two
12346 2/2/2004

--
Message posted via http://www.accessmonster.com

[Bob Quintal]

"BonnieW via AccessMonster.c om" <u33846@uwewrot e in
news:89a57df7fb 794@uwe:

There is an autonumber, as well as a date-entered, and an
entered-by-whom. So I know that the data is *before* a certain
date, sure. However, the date- entereds are from the "early
years" of the database, when people may have been entering
archival data (there's perfectly valid stuff from, say, the 1950s,
and in one case, the late 1800s). This table had been used for a
bunch of things (bad db design), so the autonumber "session" may
be bracketed by completely different data (herps vs plants, for
instance). And, to top it off, this is data from at least 5 years
ago, and the people who had entered it are long gone. :( We
*might* still have the original data forms- but since I'd be the
one stuck finding them and interpreting them and entering them,
I'm not super-keen on that method if there's an acceptable
alternate.

unfortunately, there's no date stamp for when the record was created
that's hidden in Access. You might still get close, however if your
herps and plants are a keywrd in a common field that you could
filter on.

Other than that. all I can say is good luck.

Bob Quintal wrote:

>>I inherited a table. One field in this table is Date_Observed,
and users entered data directly into this (I'm not sure there
was

[quoted text clipped - 6 lines]

>>Thanks,
Bonnie

is there some other field that will help put the dates into a
sequence? an autonumber perhaps.
e.g.

ID
12344 1/31/2004
12345 2/1/530 <- the date is between the other two
12346 2/2/2004

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

[BonnieW via AccessMonster.com]

I figured it out. :) There was some other data in the table that helped me
narrow down the range, and for this data the most important thing was more or
less the week- not a precise day- so I did a best-guess and am having the
appropriate biologists give it a once-over to see if my ballparking worked.
Thanks!

Bob Quintal wrote:

>There is an autonumber, as well as a date-entered, and an
entered-by-whom. So I know that the data is *before* a certain

[quoted text clipped - 10 lines]

>I'm not super-keen on that method if there's an acceptable
alternate.

unfortunatel y, there's no date stamp for when the record was created
that's hidden in Access. You might still get close, however if your
herps and plants are a keywrd in a common field that you could
filter on.

Other than that. all I can say is good luck.

>>>I inherited a table. One field in this table is Date_Observed,
and users entered data directly into this (I'm not sure there

[quoted text clipped - 11 lines]

>>>12345 2/1/530 <- the date is between the other two
12346 2/2/2004

--
Message posted via http://www.accessmonster.com

[Bob Quintal]

"BonnieW via AccessMonster.c om" <u33846@uwewrot e in
news:89aacd32a0 fa3@uwe:

I figured it out. :) There was some other data in the table that
helped me narrow down the range, and for this data the most
important thing was more or less the week- not a precise day- so I
did a best-guess and am having the appropriate biologists give it
a once-over to see if my ballparking worked. Thanks!

I'm happy to see you smile. Sometimes an approximation is a lot
better than totally corrupt data.

Bob Quintal wrote:

>>There is an autonumber, as well as a date-entered, and an
entered-by-whom. So I know that the data is *before* a certain

[quoted text clipped - 10 lines]

>>I'm not super-keen on that method if there's an acceptable
alternate.

unfortunately , there's no date stamp for when the record was
created that's hidden in Access. You might still get close,
however if your herps and plants are a keywrd in a common field
that you could filter on.

Other than that. all I can say is good luck.

>>>>I inherited a table. One field in this table is
Date_Observ ed, and users entered data directly into this (I'm
not sure there

[quoted text clipped - 11 lines]

>>>>12345 2/1/530 <- the date is between the other two
12346 2/2/2004

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **