criteria to get data in excel 1

[adhhealth]

hi,
my excel workbook format is
order-status sch-date act-date

i want to get external data, from another excel file-which also has same format-

i want to get data which satisfies the condition
act-date>sch-date

but in ms query i see that we cannot compare one field to the other in the criteria field.(or may be i am missing something)

ex:in ms query, i can do something like act-date>3/2/05 (which is a fixed value) but not act-date>sch-date
which is a very simple sql query

pl. advise

 

[SkipVought]

Hi,

You can compare one field to another.

If you can't do it in the Criteria Grid, open up the SQL code and insert it there -- B careful of the syntax.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

Ya i have tried doing that and i get an error "syntax error in parameter"

SELECT `Sheet1$`.`ORDER STAGES`, `Sheet1$`.`ACTUAL DATE`, `Sheet1$`.`SCHEDULED DATE`
FROM
`E:\PRODUCTION HISTORY\ORDER TRACKING\test2`.`Sheet1$` `Sheet1$`
WHERE `Sheet1$`.`ACTUAL DATE` > `Sheet1$`.`SCHEDULED DATE`

 

[SkipVought]

Do you possibly have NULL values or TEXT values in either of these date columns?

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

ya i have null values,do i have to do some kind of check??

thanks for the prompt replies Skip.

 

[adhhealth]

I am thinking of creating a new column in this file like sch-mask and have a formula like
=IF(C2="",12/30/2007,C2) where 12/30/2019 is a default value (a future date)
and compare with this column actual date> sch-mask..
i think it should work, but excel is cinverting this date :12/30/2019
to 1/0/1900
..am i in the right direction?

 

[adhhealth]

sorry =IF(C2="",12/30/2007,C2) should be
=IF(C2="",12/30/2019,C2)

 

[SkipVought]

[tt]
=IF(C2="",DATE(2019,12,30),C2)
[/tt]

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

Thanks skip it worked, is it possible to get the file name of the source too into the file along with the data?

 

[SkipVought]

Where do you want the source filename stored?

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

i am querying the data of file "a" into file "b".
i want the file name "a" also into the file "b"
ex:
filename order-status sch-date act-date
a compression 03/01/05 03/05/05

do i need to store the file name also in a cell in file "a"??
so that i can query the file name like data??

 

[SkipVought]

    sPath = "E:\PRODUCTION HISTORY\ORDER TRACKING"
    
    sFile = "test2"
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & fName & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "SELECT S.`ORDER STAGES`, S.`ACTUAL DATE`, S.`SCHEDULED DATE`, " & sFile
    sSQL sSQL & " FROM `" & sPath & "\" & sFile & "`.`Sheet1$` S "
    sSQL sSQL & "WHERE S.`ACTUAL DATE` > S.`SCHEDULED DATE` "
    
    With Sheets("YourQTSheet").QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[adhhealth]

I am getting a compile error for the code:
"Compile error: Expected Sub Function or Property"
at this line:

sSql sSql & " FROM `" & sPath & "\" & sFile & "`.`Sheet1$`"
sSql sSql & "WHERE `Sheet1$`.`ACTUAL DATE` > `Sheet1$`.sch "

With Sheets("YourQTSheet").QueryTables(1)
.Connection = sConn
.CommandText = sSql
.Refresh BackgroundQuery:=False
End With
End Sub

I pasted this code in a macro. was I supposed to copy this code somewhere else??
i have no clue..

this the code i have entered:

Sub dm()

sPath = "E:\PRODUCTION HISTORY\ORDER TRACKING"

sFile = "test3"

sConn = "ODBC;DSN=Excel Files;"
sConn = sConn & "DBQ=" & sPath & "\" & sFile & ".xls;"
sConn = sConn & "DefaultDir=" & sPath & ";"
sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

sSql = "SELECT `Sheet1$`.`ORDER STAGES`, `Sheet1$`.`ACTUAL DATE`, `Sheet1$`.`SCHEDULED DATE`, `Sheet1$`.sch," & sFile
sSql sSql & " FROM `" & sPath & "\" & sFile & "`.`Sheet1$`"
sSql sSql & "WHERE `Sheet1$`.`ACTUAL DATE` > `Sheet1$`.sch "

With Sheets("YourQTSheet").QueryTables(1)
.Connection = sConn
.CommandText = sSql
.Refresh BackgroundQuery:=False
End With
End Sub

 

[SkipVought]

    sPath = "E:\PRODUCTION HISTORY\ORDER TRACKING"
    
    sFile = "test2"
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & fName & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "SELECT S.`ORDER STAGES`, S.`ACTUAL DATE`, S.`SCHEDULED DATE`, " & sFile
    sSQL = sSQL & " FROM `" & sPath & "\" & sFile & "`.`Sheet1$` S "
    sSQL = sSQL & "WHERE S.`ACTUAL DATE` > S.`SCHEDULED DATE` "
    
    With Sheets("YourQTSheet").QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}