Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Query Parameter from Worksheet Cell
My query works fine in MS Query with fixed parameters but I'm having trouble
figuring out the syntax to substitute fixed values with getting one from a cell. I have searched through this forum and found a lot of great info about using parameter queries and the easy way to set up Excel to pull cell info into a query. Unfortunatley that doesn't work with my query. Since it has a subquery and a union, I can't use MS Query to edit it. I pulled it into Excel and opened it with MS Script Editor, then put a ? where the fixed parameter is, but there is an identifed bug that keeps this from working (http://support.microsoft.com/kb/293790/en-us - PS I have the latest MDAC and still get the errors). I beleive it may work if I pull it from a cell instead of having the user input it. In the query below, I want to substitute 'CVASP-294/P%' in four places with a single cell value from A1 on worksheet "Card Access Attestation Report". Any help would be appreciated. <x:ConnectionDSN=PW_Prod;Description=ProWatch Production Database;UID=A9WS2;APP=Microsoft Office 2003;WSID=CNU5320W57;DATABASE=PWNT;Network=</x:Connection <x:ConnectionDBMSSOCN;Trusted_Connection=Yes</x:Connection <x:CommandTextSELECT DISTINCT BADGE.LNAME AS "Last Name", BADGE.FNAME AS "First Name", BADGE_V.EMPID AS "EmpID#", </x:CommandText <x:CommandTextSUBSTRING(BADGE_V.CARD_TYPE,4,50) AS "Badge Type", COMPANY.NAM AS "Company", BADGE_V.DEPARTMENT AS "Department", </x:CommandText <x:CommandTextBADGE_V.CITY AS "City", CAST(BADGE_V.BADGE_NUMBER*10000 as int) AS "Badge#", MAX(CONVERT(varchar,BADGE_C.LAST_ACC,101)) AS </x:CommandText <x:CommandText"Last Use", SUBSTRING(CLEAR.DESCRP,1,9) AS "Space Code" </x:CommandText <x:CommandTextFROM PWNT.dbo.BADGE BADGE, PWNT.dbo.BADGE_C BADGE_C, PWNT.dbo.BADGE_CC BADGE_CC, </x:CommandText <x:CommandTextPWNT.dbo.BADGE_STATUS BADGE_STATUS, PWNT.dbo.BADGE_V BADGE_V, PWNT.dbo.CLEAR CLEAR, PWNT.dbo.COMPANY COMPANY </x:CommandText <x:CommandTextWHERE BADGE.ID = BADGE_V.ID AND BADGE_STATUS.ID = BADGE.BADGE_STATUS AND BADGE_C.ID = BADGE_V.ID AND COMPANY.ID = BADGE_V.COMPANY </x:CommandText <x:CommandTextAND BADGE_STATUS.DESCRP='Active' AND CLEAR.ID = BADGE_CC.CLEAR_COD AND CLEAR.DESCRP Like 'CVASP-294/P%' </x:CommandText <x:CommandTextAND BADGE_C.ID in (SELECT DISTINCT BADGE_C.ID FROM PWNT.dbo.BADGE_C BADGE_C, PWNT.dbo.BADGE_CC BADGE_CC, PWNT.dbo.CLEAR CLEAR </x:CommandText <x:CommandTextWHERE CLEAR.DESCRP Like 'CVASP-294/P%' AND BADGE_C.STAT_COD = 'A' AND BADGE_CC.CARDNO = BADGE_C.CARDNO AND CLEAR.ID = BADGE_CC.CLEAR_COD) </x:CommandText <x:CommandTextGROUP BY BADGE.LNAME, BADGE.FNAME, BADGE_V.EMPID, SUBSTRING(BADGE_V.CARD_TYPE,4,50), COMPANY.NAM, BADGE_V.DEPARTMENT, </x:CommandText <x:CommandTextBADGE_V.CITY, CAST(BADGE_V.BADGE_NUMBER*10000 as int), SUBSTRING(CLEAR.DESCRP,1,9) </x:CommandText <x:CommandTextUNION </x:CommandText <x:CommandTextSELECT DISTINCT BADGE.LNAME AS "Last Name", BADGE.FNAME AS "First Name", BADGE_V.EMPID AS "EmpID#", </x:CommandText <x:CommandTextSUBSTRING(BADGE_V.CARD_TYPE,4,50) AS "Badge Type", '<<NONE' as "Company", </x:CommandText <x:CommandTextBADGE_V.DEPARTMENT AS "Department", BADGE_V.CITY AS "City", CAST(BADGE_V.BADGE_NUMBER*10000 as int) AS "Badge#", </x:CommandText <x:CommandTextMAX(CONVERT(varchar,BADGE_C.LAST_AC C,101)) AS "Last Use", SUBSTRING(CLEAR.DESCRP,1,9) AS "Space Code" </x:CommandText <x:CommandTextFROM PWNT.dbo.BADGE BADGE, PWNT.dbo.BADGE_C BADGE_C, PWNT.dbo.BADGE_CC BADGE_CC, PWNT.dbo.BADGE_STATUS BADGE_STATUS, PWNT.dbo.BADGE_V BADGE_V, </x:CommandText <x:CommandTextPWNT.dbo.CLEAR CLEAR </x:CommandText <x:CommandTextWHERE BADGE.ID = BADGE_V.ID AND BADGE_STATUS.ID = BADGE.BADGE_STATUS AND BADGE_C.ID = BADGE_V.ID AND BADGE_V.COMPANY IS NULL </x:CommandText <x:CommandTextAND BADGE_STATUS.DESCRP='Active' AND CLEAR.ID = BADGE_CC.CLEAR_COD AND CLEAR.DESCRP Like 'CVASP-294/P%' </x:CommandText <x:CommandTextAND BADGE_C.ID in (SELECT DISTINCT BADGE_C.ID FROM PWNT.dbo.BADGE_C BADGE_C, PWNT.dbo.BADGE_CC BADGE_CC, PWNT.dbo.CLEAR CLEAR </x:CommandText <x:CommandTextWHERE CLEAR.DESCRP Like 'CVASP-294/P%' AND BADGE_C.STAT_COD = 'A' AND BADGE_CC.CARDNO = BADGE_C.CARDNO AND CLEAR.ID = BADGE_CC.CLEAR_COD) </x:CommandText <x:CommandTextGROUP BY BADGE.LNAME, BADGE.FNAME, BADGE_V.EMPID, SUBSTRING(BADGE_V.CARD_TYPE,4,50), BADGE_V.DEPARTMENT, </x:CommandText <x:CommandTextBADGE_V.CITY, CAST(BADGE_V.BADGE_NUMBER*10000 as int), SUBSTRING(CLEAR.DESCRP,1,9) </x:CommandText <x:CommandTextORDER BY BADGE.LNAME, BADGE.FNAME, CAST(BADGE_V.BADGE_NUMBER*10000 as int)</x:CommandText -- -- Bill |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Query Parameter from Worksheet Cell
Bill,
It looks like you may need quotation marks around the value. Try creating a variable and setting it equal to the cell value. In the query try &" "' " & at each end of your varaible. In the query it will appear Like "myvariable" "Bill" wrote: My query works fine in MS Query with fixed parameters but I'm having trouble figuring out the syntax to substitute fixed values with getting one from a cell. I have searched through this forum and found a lot of great info about using parameter queries and the easy way to set up Excel to pull cell info into a query. Unfortunatley that doesn't work with my query. Since it has a subquery and a union, I can't use MS Query to edit it. I pulled it into Excel and opened it with MS Script Editor, then put a ? where the fixed parameter is, but there is an identifed bug that keeps this from working (http://support.microsoft.com/kb/293790/en-us - PS I have the latest MDAC and still get the errors). I beleive it may work if I pull it from a cell instead of having the user input it. In the query below, I want to substitute 'CVASP-294/P%' in four places with a single cell value from A1 on worksheet "Card Access Attestation Report". Any help would be appreciated. <x:ConnectionDSN=PW_Prod;Description=ProWatch Production Database;UID=A9WS2;APP=Microsoft Office 2003;WSID=CNU5320W57;DATABASE=PWNT;Network=</x:Connection <x:ConnectionDBMSSOCN;Trusted_Connection=Yes</x:Connection <x:CommandTextSELECT DISTINCT BADGE.LNAME AS "Last Name", BADGE.FNAME AS "First Name", BADGE_V.EMPID AS "EmpID#", </x:CommandText <x:CommandTextSUBSTRING(BADGE_V.CARD_TYPE,4,50) AS "Badge Type", COMPANY.NAM AS "Company", BADGE_V.DEPARTMENT AS "Department", </x:CommandText <x:CommandTextBADGE_V.CITY AS "City", CAST(BADGE_V.BADGE_NUMBER*10000 as int) AS "Badge#", MAX(CONVERT(varchar,BADGE_C.LAST_ACC,101)) AS </x:CommandText <x:CommandText"Last Use", SUBSTRING(CLEAR.DESCRP,1,9) AS "Space Code" </x:CommandText <x:CommandTextFROM PWNT.dbo.BADGE BADGE, PWNT.dbo.BADGE_C BADGE_C, PWNT.dbo.BADGE_CC BADGE_CC, </x:CommandText <x:CommandTextPWNT.dbo.BADGE_STATUS BADGE_STATUS, PWNT.dbo.BADGE_V BADGE_V, PWNT.dbo.CLEAR CLEAR, PWNT.dbo.COMPANY COMPANY </x:CommandText <x:CommandTextWHERE BADGE.ID = BADGE_V.ID AND BADGE_STATUS.ID = BADGE.BADGE_STATUS AND BADGE_C.ID = BADGE_V.ID AND COMPANY.ID = BADGE_V.COMPANY </x:CommandText <x:CommandTextAND BADGE_STATUS.DESCRP='Active' AND CLEAR.ID = BADGE_CC.CLEAR_COD AND CLEAR.DESCRP Like 'CVASP-294/P%' </x:CommandText <x:CommandTextAND BADGE_C.ID in (SELECT DISTINCT BADGE_C.ID FROM PWNT.dbo.BADGE_C BADGE_C, PWNT.dbo.BADGE_CC BADGE_CC, PWNT.dbo.CLEAR CLEAR </x:CommandText <x:CommandTextWHERE CLEAR.DESCRP Like 'CVASP-294/P%' AND BADGE_C.STAT_COD = 'A' AND BADGE_CC.CARDNO = BADGE_C.CARDNO AND CLEAR.ID = BADGE_CC.CLEAR_COD) </x:CommandText <x:CommandTextGROUP BY BADGE.LNAME, BADGE.FNAME, BADGE_V.EMPID, SUBSTRING(BADGE_V.CARD_TYPE,4,50), COMPANY.NAM, BADGE_V.DEPARTMENT, </x:CommandText <x:CommandTextBADGE_V.CITY, CAST(BADGE_V.BADGE_NUMBER*10000 as int), SUBSTRING(CLEAR.DESCRP,1,9) </x:CommandText <x:CommandTextUNION </x:CommandText <x:CommandTextSELECT DISTINCT BADGE.LNAME AS "Last Name", BADGE.FNAME AS "First Name", BADGE_V.EMPID AS "EmpID#", </x:CommandText <x:CommandTextSUBSTRING(BADGE_V.CARD_TYPE,4,50) AS "Badge Type", '<<NONE' as "Company", </x:CommandText <x:CommandTextBADGE_V.DEPARTMENT AS "Department", BADGE_V.CITY AS "City", CAST(BADGE_V.BADGE_NUMBER*10000 as int) AS "Badge#", </x:CommandText <x:CommandTextMAX(CONVERT(varchar,BADGE_C.LAST_AC C,101)) AS "Last Use", SUBSTRING(CLEAR.DESCRP,1,9) AS "Space Code" </x:CommandText <x:CommandTextFROM PWNT.dbo.BADGE BADGE, PWNT.dbo.BADGE_C BADGE_C, PWNT.dbo.BADGE_CC BADGE_CC, PWNT.dbo.BADGE_STATUS BADGE_STATUS, PWNT.dbo.BADGE_V BADGE_V, </x:CommandText <x:CommandTextPWNT.dbo.CLEAR CLEAR </x:CommandText <x:CommandTextWHERE BADGE.ID = BADGE_V.ID AND BADGE_STATUS.ID = BADGE.BADGE_STATUS AND BADGE_C.ID = BADGE_V.ID AND BADGE_V.COMPANY IS NULL </x:CommandText <x:CommandTextAND BADGE_STATUS.DESCRP='Active' AND CLEAR.ID = BADGE_CC.CLEAR_COD AND CLEAR.DESCRP Like 'CVASP-294/P%' </x:CommandText <x:CommandTextAND BADGE_C.ID in (SELECT DISTINCT BADGE_C.ID FROM PWNT.dbo.BADGE_C BADGE_C, PWNT.dbo.BADGE_CC BADGE_CC, PWNT.dbo.CLEAR CLEAR </x:CommandText <x:CommandTextWHERE CLEAR.DESCRP Like 'CVASP-294/P%' AND BADGE_C.STAT_COD = 'A' AND BADGE_CC.CARDNO = BADGE_C.CARDNO AND CLEAR.ID = BADGE_CC.CLEAR_COD) </x:CommandText <x:CommandTextGROUP BY BADGE.LNAME, BADGE.FNAME, BADGE_V.EMPID, SUBSTRING(BADGE_V.CARD_TYPE,4,50), BADGE_V.DEPARTMENT, </x:CommandText <x:CommandTextBADGE_V.CITY, CAST(BADGE_V.BADGE_NUMBER*10000 as int), SUBSTRING(CLEAR.DESCRP,1,9) </x:CommandText <x:CommandTextORDER BY BADGE.LNAME, BADGE.FNAME, CAST(BADGE_V.BADGE_NUMBER*10000 as int)</x:CommandText -- -- Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value also display in a selected cell on a worksheet | Excel Discussion (Misc queries) | |||
Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value also display in a selected cell on a worksheet | Excel Worksheet Functions | |||
Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value also display in a selected cell on a worksheet | Excel Programming | |||
Pull parameter from worksheet for External Data Query to SQL Datab | Excel Discussion (Misc queries) | |||
SQL parameter query - more than cell value | Excel Programming |