Getting Query Parameter from Worksheet Cell

[Guest]

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:Connection>DSN=PW_Prod;Description=ProWatch Production
Database;UID=A9WS2;APP=Microsoft Office
2003;WSID=CNU5320W57;DATABASE=PWNT;Network=</x:Connection>
<x:Connection>DBMSSOCN;Trusted_Connection=Yes</x:Connection>
<x:CommandText>SELECT DISTINCT BADGE.LNAME AS "Last Name", BADGE.FNAME
AS "First Name", BADGE_V.EMPID AS "EmpID#", </x:CommandText>
<x:CommandText>SUBSTRING(BADGE_V.CARD_TYPE,4,50) AS "Badge Type",
COMPANY.NAM AS "Company", BADGE_V.DEPARTMENT AS "Department", </x:CommandText>
<x:CommandText>BADGE_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:CommandText>FROM PWNT.dbo.BADGE BADGE, PWNT.dbo.BADGE_C BADGE_C,
PWNT.dbo.BADGE_CC BADGE_CC, </x:CommandText>
<x:CommandText>PWNT.dbo.BADGE_STATUS BADGE_STATUS, PWNT.dbo.BADGE_V
BADGE_V, PWNT.dbo.CLEAR CLEAR, PWNT.dbo.COMPANY COMPANY </x:CommandText>
<x:CommandText>WHERE 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:CommandText>AND BADGE_STATUS.DESCRP='Active' AND CLEAR.ID =
BADGE_CC.CLEAR_COD AND CLEAR.DESCRP Like 'CVASP-294/P%' </x:CommandText>
<x:CommandText>AND 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:CommandText>WHERE 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:CommandText>GROUP BY BADGE.LNAME, BADGE.FNAME, BADGE_V.EMPID,
SUBSTRING(BADGE_V.CARD_TYPE,4,50), COMPANY.NAM, BADGE_V.DEPARTMENT,
</x:CommandText>
<x:CommandText>BADGE_V.CITY, CAST(BADGE_V.BADGE_NUMBER*10000 as int),
SUBSTRING(CLEAR.DESCRP,1,9) </x:CommandText>
<x:CommandText>UNION </x:CommandText>
<x:CommandText>SELECT DISTINCT BADGE.LNAME AS "Last Name", BADGE.FNAME
AS "First Name", BADGE_V.EMPID AS "EmpID#", </x:CommandText>
<x:CommandText>SUBSTRING(BADGE_V.CARD_TYPE,4,50) AS "Badge Type",
'<<NONE>>' as "Company", </x:CommandText>
<x:CommandText>BADGE_V.DEPARTMENT AS "Department", BADGE_V.CITY AS
"City", CAST(BADGE_V.BADGE_NUMBER*10000 as int) AS "Badge#", </x:CommandText>
<x:CommandText>MAX(CONVERT(varchar,BADGE_C.LAST_ACC,101)) AS "Last
Use", SUBSTRING(CLEAR.DESCRP,1,9) AS "Space Code" </x:CommandText>
<x:CommandText>FROM 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:CommandText>PWNT.dbo.CLEAR CLEAR </x:CommandText>
<x:CommandText>WHERE 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:CommandText>AND BADGE_STATUS.DESCRP='Active' AND CLEAR.ID =
BADGE_CC.CLEAR_COD AND CLEAR.DESCRP Like 'CVASP-294/P%' </x:CommandText>
<x:CommandText>AND 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:CommandText>WHERE 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:CommandText>GROUP BY BADGE.LNAME, BADGE.FNAME, BADGE_V.EMPID,
SUBSTRING(BADGE_V.CARD_TYPE,4,50), BADGE_V.DEPARTMENT, </x:CommandText>
<x:CommandText>BADGE_V.CITY, CAST(BADGE_V.BADGE_NUMBER*10000 as int),
SUBSTRING(CLEAR.DESCRP,1,9) </x:CommandText>
<x:CommandText>ORDER BY BADGE.LNAME, BADGE.FNAME,
CAST(BADGE_V.BADGE_NUMBER*10000 as int)</x:CommandText>

 

[Guest]

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"