sql question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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......
 
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.
 
Back
Top