QueryTables command

  • Thread starter Sharlene England
  • Start date
S

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top