Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query from Access into Excel cause Access to go to read only | Excel Discussion (Misc queries) | |||
Applying Criteria in Excel Cell to MS Query Pull from Access | Excel Discussion (Misc queries) | |||
Can I use MS Query in Excel like an Append Query in Access | Excel Discussion (Misc queries) | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |