Using InputBox to change query criteria in Access

J

JohnBoy

So I'm trying to use the value from the inpubox, called
INDCODE here, to change the criteria for an external data
pull from Access. INDCODE is recongnized [in WHERE
(`Invoice Summary Query`.EXPENSE_INDEX_CODE=INDCODE)] as
the I can put my cursor on it after that step and see that
the value takes, but I get a general ODBC error on the
last step - .Refresh BackgroundQuery:=False

Help and thx in advance!

Message = "Please input the index code"
Title = "ICI report generator"
INDCODE = InputBox(Message, Title, "", 5250, 5250)
With ActiveSheet.QueryTables.Add(Connection:=Array
(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\jboy\Desktop\ICIREPORTING.mdb;DefaultDir=C:\Docume
nts and Settings\jboy" _
), Array("e\Desktop;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A2"))
.CommandText = Array( _
"SELECT `Invoice Summary Query`.BILLING_DATE,
`Invoice Summary Query`.SERVICE_NAME, `Invoice Summary
Query`.DESCRIPTION, `Invoice Summary
Query`.REQUESTOR_FIRST_NAME, `Invoice Summary
Query`.REQUESTOR_LAST_NAME, `Invoice Summary Qu" _
, _
"ery`.AUTHORIZER_FIRST_NAME, `Invoice Summary
Query`.AUTHORIZER_LAST_NAME, `Invoice Summary
Query`.EXPENSE_JE_NUMBER, `Invoice Summary
Query`.EXPENSE_JE_SUFFIX, `Invoice Summary
Query`.REVENUE_JE_NUMBER, `Invoice Summary Query`.REV" _
, _
"ENUE_JE_SUFFIX, `Invoice Summary
Query`.EXPENSE_SUBOBJECT_CODE, `Invoice Summary
Query`.AMOUNT_BILLED, `Invoice Summary
Query`.EXPENSE_INDEX_CODE FROM `C:\Documents and
Settings\jboy\Desktop\ICIREPORTING`.`Invoice Summary
Query" _
, _
"` `Invoice Summary Query` WHERE (`Invoice
Summary Query`.EXPENSE_INDEX_CODE=INDCODE) ORDER BY
`Invoice Summary Query`.BILLING_DATE, `Invoice Summary
Query`.EXPENSE_SUBOBJECT_CODE, `Invoice Summary
Query`.AMOUNT_BILLED" _
)
.Name = "Copy of ICI billing from Access_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Documents and Settings\jboy\Application
Data\Microsoft\Queries\Copy of ICI billing from Access.dqy"
.Refresh BackgroundQuery:=False
End With
 
J

johnboy

Inputbox is a string and works fine when hardcoded, though.
-----Original Message-----
Hi,



InputBox return a string and it seem that you need a number.






--
JP
(e-mail address removed)
http://www.solutionsvba.com


JohnBoy said:
So I'm trying to use the value from the inpubox, called
INDCODE here, to change the criteria for an external data
pull from Access. INDCODE is recongnized [in WHERE
(`Invoice Summary Query`.EXPENSE_INDEX_CODE=INDCODE)] as
the I can put my cursor on it after that step and see that
the value takes, but I get a general ODBC error on the
last step - .Refresh BackgroundQuery:=False

Help and thx in advance!

Message = "Please input the index code"
Title = "ICI report generator"
INDCODE = InputBox(Message, Title, "", 5250, 5250)
With ActiveSheet.QueryTables.Add(Connection:=Array
(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\jboy\Desktop\ICIREPORTING.mdb;DefaultDir=C:\Docume
nts and Settings\jboy" _
), Array("e\Desktop;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A2"))
.CommandText = Array( _
"SELECT `Invoice Summary Query`.BILLING_DATE,
`Invoice Summary Query`.SERVICE_NAME, `Invoice Summary
Query`.DESCRIPTION, `Invoice Summary
Query`.REQUESTOR_FIRST_NAME, `Invoice Summary
Query`.REQUESTOR_LAST_NAME, `Invoice Summary Qu" _
, _
"ery`.AUTHORIZER_FIRST_NAME, `Invoice Summary
Query`.AUTHORIZER_LAST_NAME, `Invoice Summary
Query`.EXPENSE_JE_NUMBER, `Invoice Summary
Query`.EXPENSE_JE_SUFFIX, `Invoice Summary
Query`.REVENUE_JE_NUMBER, `Invoice Summary Query`.REV" _
, _
"ENUE_JE_SUFFIX, `Invoice Summary
Query`.EXPENSE_SUBOBJECT_CODE, `Invoice Summary
Query`.AMOUNT_BILLED, `Invoice Summary
Query`.EXPENSE_INDEX_CODE FROM `C:\Documents and
Settings\jboy\Desktop\ICIREPORTING`.`Invoice Summary
Query" _
, _
"` `Invoice Summary Query` WHERE (`Invoice
Summary Query`.EXPENSE_INDEX_CODE=INDCODE) ORDER BY
`Invoice Summary Query`.BILLING_DATE, `Invoice Summary
Query`.EXPENSE_SUBOBJECT_CODE, `Invoice Summary
Query`.AMOUNT_BILLED" _
)
.Name = "Copy of ICI billing from Access_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Documents and Settings\jboy\Application
Data\Microsoft\Queries\Copy of ICI billing from Access.dqy"
.Refresh BackgroundQuery:=False
End With
Click to expand...


.
Click to expand...
 
Top