predicting and filtering non-date records in a field query

[John1Chr]

Hi all,

Just curious if there is a function that can predict and filter out or ignore anything that is a nondate.

I'm downloading mainframe dates and within a text field A: MMDDYY I'm using the parsing command to give the format MM/DD/YYYY in field b:

The command for field b: CDate(Left(Trim([table A]![Field A]),2) & "/" & Mid([table A]![Field A],4,2) & "/" & Right([table A]![Field A],2))

Problem is when I try to sort and their is anything that is trash or not really a date like "91/12/00" And "91/22/00" or blank I get the Data type mismatch error. I've been doing a work around by having a field off to the right that has <>"91/12/00" and <> "91/22/00" and <>"".

The main problem is that it is hard to anticipate what non-date record is coming down the shute.

 

[SkipVought]

Hi,

Where Mid([MainframeDate],1,2) Between '01' AND '12'

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Golom]

I'm not sure of exactly how you are doing this (i.e. what the SQL looks like) but you might try a where clause to filter out the non-date records

WHERE IsDate(Left(Trim([table A]![Field A]),2) & "/" & 
             Mid([table A]![Field A],4,2) & "/" & 
             Right([table A]![Field A],2)))

If you want to keep them but have the date appear as a valid date then

b: CDate(
         IIF(IsDate(Left(Trim([table A]![Field A]),2) & "/" & 
                    Mid([table A]![Field A],4,2) & "/" & 
                    Right([table A]![Field A],2)),

             Left(Trim([table A]![Field A]),2) & "/" & 
             Mid([table A]![Field A],4,2) & "/" & 
             Right([table A]![Field A],2)),

             0 )
          )

Which will default invalid dates to Dec 30, 1899. You should be able to filter that out easily.

 

[John1Chr]

Interesting....I had to use the trim function to knock off the spaces in front. It's giving me two records less than I had so it must be filtering out some garble di gook soup that I wasn't aware of.

Thanks.

 

[John1Chr]

My post was for Skip....Golom, didn't try yours yet.