Using InputBox to change query criteria in Access
 

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

Using InputBox to change query criteria in Access
 

Hi,



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






--
JP

http://www.solutionsvba.com


"JohnBoy" wrote in message
...
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

Using InputBox to change query criteria in Access
 

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

http://www.solutionsvba.com


"JohnBoy" wrote in message
...
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


.