sql question

[JT]

The dates in a text field are all 6 characters long,e.g., 060107, 1019/07.
060505, etc. Some dates also appear as 000OCT, 000AUG, 000SEP, etc.

I'm trying use sql code to extract records from a database. The code is as
follows:

vSQL = "SELECT [CUST],[SOURCE],[DEPT] from [Current] WHERE [CUST] = '" &
CSTMR & "'AND ([DATE] <= '" & DateVar & "' AND mid([DATE],1,3) <> '" &
DateVar1 & "')"

CSTMR = "9999"
DateVar = "010107"
DateVar1 = "000"

I'm trying to extract the records that are older than 01/01/07 but am not
having much luck. I thought it would work if the DATE field was all 6
characters long but I'm not finding that to be true.

In Excel "080705" is less than "080707" but I can't extract these same
records with the sql statement. Any help would be appreciated. Thanks......

 

[Ken Snell \(MVP\)]

The problem here is that you have two completely different formats for the
dates, and you need to convert each one to a single date format field, and
then you can filter on that field.

I am not sure what date would be meant by the value "000SEP"? What year and
day would go with this September month name?

Give us more details about this non-standard data value, and then we can
give more specific suggestions.

In the meantime, here is an example of what I suggest in the first sentence,
using the mmddyy format values:

SELECT [CUST], [SOURCE], [DEPT]
FROM [Current]
WHERE (DateSerial(Right([DATE], 2), Left([DATE], 2),
Mid([DATE], 3, 2)) < DateSerial(2007, 1, 1);

The above SQL statement could be modified to convert the other date values
once we know how to interpret them.