Date Question

[Gary F Shelton]

I have pulled some fields out of our mainframe which are:
Century > Year > Month > Day
So in Access I first tried to perform a Cdate of Date:
cdate([Month]&"/"&[Day]&"?"&[Year]) but it didn't work.. It didn't work
because I have learned that some of the records in the data are subject to
being wrong because a human has typed them in wrong. Meaning they made the
month 13 when that should of been the day field or they made the month 00 ...
So I am not looking to research these incorrect fields but rather by pass
them. Any ideas on how to bypass them....

I tried on the month - Between 1 And 12
I tried on the day - Between 1 And 31

I still have an issues where it states data type error... As I have spot
chedked the data I have found a few records like last years leap year where
someone typed 02/30/2008..... With the criteria limitation that I had
previously typed it won't exclude this record...

any help is greatly appreciated...

 

[Jerry Whittle]

Use the IsDate function in place of the CDate below. It returns a 0 for False
and -1 is True. You could put it in a query and criteria of -1 or True
without quotes.

Something I often do is use an IIf statement to put in a bogus date. That
way the record can still be seen, and maybe the data fixed. Below would show
1/1/1950 if not a valid date. BTW: I noticed that you had a ? instead of the
/ . Hopefully that's just a typo here.

IIf(IsDate([Month]&"/"&[Day]&"/"&[Year])=True,CDate([Month]&"/"&[Day]&"/"&[Year]),#1/1/1950#) AS NotDate

 

[Gary]

FYI: Here is some of the raw data

Correct entries look like:
20010208 RESULT - 2/8/2001
20020907 RESULT - 9/7/2002
20030303 RESULT - 3/3/2003
Incorrect Entries look like:

..0090208 Result - #error
03 17 09 Result - #error
03 18 09 Result - #error
03/17/09 Result - #error
03/18/09 Result - #error
200019857 Result - #error
20080310E Result - #error
20081202' Result - #error
20081250 Result - #error
20090119' Result - #error
A0081208 Result - #error

 

[Gary]

Jerry, I tried the solution and it didn't work... still returns the data
mismatch error...
--
GS

Use the IsDate function in place of the CDate below. It returns a 0 for False
and -1 is True. You could put it in a query and criteria of -1 or True
without quotes.

Something I often do is use an IIf statement to put in a bogus date. That
way the record can still be seen, and maybe the data fixed. Below would show
1/1/1950 if not a valid date. BTW: I noticed that you had a ? instead of the
/ . Hopefully that's just a typo here.

IIf(IsDate([Month]&"/"&[Day]&"/"&[Year])=True,CDate([Month]&"/"&[Day]&"/"&[Year]),#1/1/1950#) AS NotDate
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have pulled some fields out of our mainframe which are:
Century > Year > Month > Day
So in Access I first tried to perform a Cdate of Date:
cdate([Month]&"/"&[Day]&"?"&[Year]) but it didn't work.. It didn't work
because I have learned that some of the records in the data are subject to
being wrong because a human has typed them in wrong. Meaning they made the
month 13 when that should of been the day field or they made the month 00 ...
So I am not looking to research these incorrect fields but rather by pass
them. Any ideas on how to bypass them....

I tried on the month - Between 1 And 12
I tried on the day - Between 1 And 31

I still have an issues where it states data type error... As I have spot
chedked the data I have found a few records like last years leap year where
someone typed 02/30/2008..... With the criteria limitation that I had
previously typed it won't exclude this record...

any help is greatly appreciated...

 

[John Spencer]

Try this. It should work as an expression in a query. In VBA you can't
use an immediate if (IIF) but will need to use the If..Then..End If
multi-line construct.

IIF(IsDate(Format([DateField],"@@@@/@@/@@")),CDate(Format([DateField],""@@@@/@@/@@")),
Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================