QueryTables command

[Sharlene England]

The following works properly, BUT when I add a WHERE clause to my
..commandtext I get an error 400.

IF I REPLACE THE .CommandText line with this it doesnt work
.CommandText = Array("SELECT CUSTCODE, PROJ_DESCR, PO_NUMBER, DATE,
PROJMAN, CUSTFORCD FROM `k:\home\sharlene`\job_no.dbf WHERE(JOB_NO=" &
Worksheets("Creation").Range("B3").Value & ")"

What am I doing wrong?


With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;CollatingSequence=ASCII;DBQ=K:\home\sharlene;DefaultDir=K:\home\sharlene;Deleted=1;Driver={Microsoft
dBase Driver (" _
), Array( _
"*.dbf)};DriverId=533;FIL=dBase
5.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=600;SafeTransactions=0;Statistics=0;Threads=3;U"
_
), Array("serCommitSync=Yes;")), Destination:=Range("B27"))
.CommandText = Array("SELECT CUSTCODE, PROJ_DESCR, PO_NUMBER, DATE,
PROJMAN, CUSTFORCD FROM `k:\home\sharlene`\job_no.dbf ") <<THIS
LINE WORKS AS IS
.Name = "realjob"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Thanks.

 

[Tim Williams]

Is the JOB_NO field numeric? If not then the value in the query should be
in quotes.
Is the value in B3 numeric?

Try using debug.print to print the final SQL to the immediate window. Copy
and paste it to your query tool and see what happens when you run it.


'*****************************
dim sSQL as string

sSQL= "SELECT CUSTCODE, PROJ_DESCR, PO_NUMBER, DATE, " & _
" PROJMAN, CUSTFORCD FROM `k:\home\sharlene`\job_no.dbf " & _
" WHERE JOB_NO=" & Worksheets("Creation").Range("B3").Value

debug.print sSQL

' rest of code....
.CommandText = Array(sSQL)
'etc

'******************************



Tim