VBA Application.Inputbox to Excel SQL Statement

[Devas]

I'm trying to allow the user to choose which cell to select his SQL criteria, but after the user selects Cell A1 to set myRange to it, it won't fill at the bottom where it says PDDOCO=myRange. can anyone help? The error is General ODBC Error and the line that is highlighted is ".Refresh BackgroundQuery:=False"

Sub TestInputBox()
Dim myRange As Range

Set myRange = Application.InputBox(Prompt:= _
"Please Select a Range", _
Title:="InputBox Method", Type:=8)


With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=S102XKXM;DBQ=QGPL DTA;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM)" _
), Array(",2,0,1,0,512;TRANSLATE=1;")), Destination:=Range("$A$2")).QueryTable
.CommandText = Array( _
"SELECT F4311.PDTRDJ, F4311.PDDOCO, F4311.PDLNID, F4311.PDLITM, F4311.PDAITM, F4311.PDVR01, F4311.PDVR02, F4311.PDPDDJ, F4311.PDUORG, F4311.PDUOPN, F4311.PDPRRC/10000" & Chr(13) & "" & Chr(10) & "FROM S102XKXM.DTA.F4101 F4101," _
, _
" S102XKXM.DTA.F4311 F4311" & Chr(13) & "" & Chr(10) & "WHERE F4311.PDITM = F4101.IMITM AND ((F4311.PDDOCO=myRange) AND (F4311.PDLNTY='S') AND (F4311.PDNXTR='400'))" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Current_Purchases"
.Refresh BackgroundQuery:=False
End With
End Sub

 

[ChrisM]

Ugh, AS400 queries, I don't miss those! Your issue is you have not used &'s and quotes to append the value in myRange to your sql text

1. This is a text value, so you need to wrap it with single quotes

2. You are trying to add the value from a variable to your text string, you must do this with &s and double quotes.

AND ((F4311.PDDOCO= '" & myRange.Value & "') AND (F4311.PDLNTY='S')

 

[Devas]

Thank you so much!