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

--
JT

When you say you're not having much luck, what does that mean? Does it
run but pull the wrong records (or no records), or does it generate a
specific error?

I've only used SQL from Excel a few times, but one note I'd have is
that T-SQL doesn't recognize MID as a string function. You use
SUBSTRING in the same way, though.

On Nov 12, 4:56 pm, JT wrote:
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......

--
JT