running SQL in vb

[Apples76]

i have a query that i would like to run when i chage a value in a combo box
(this bit works) however i am having trouble converting the SQL to use in vb.
the example that i have in a referance book is for making a crosstab query
using DAO, hence i am using DAO

my sql query is below. could someone tell me if it is possible to do, and if
so how to modify it.

many thanks.

Peter

SELECT Master_KPI_qry.CSR_Number, Master_KPI_qry.Option_Name,
Master_KPI_qry.Nom_Date, Master_KPI_qry.[Date Of Nom Visit],
Master_KPI_qry.Pln_dwg_Rec, DateDiff("d",[Date Of Nom Visit],Now())-7 AS
[Days Overdue], Master_KPI_qry.Contractor_Name
FROM Master_KPI_qry
WHERE (((Master_KPI_qry.Nom_Date) Is Not Null) AND
((Master_KPI_qry.Pln_dwg_Rec) Is Null) AND ((DateDiff("d",[Date Of Nom
Visit],Now())-7)>=0) AND
((Master_KPI_qry.Contractor_Name)=[Forms]![company_filter_tbl]![Combo2]))
ORDER BY Master_KPI_qry.Contractor_Name, Master_KPI_qry.CSR_Number;

 

[John Spencer]

To construct the string
-- double all the quote marks that are internal to the string.
-- include the value of the combobox (string needs quote marks also)
-- Optionally drop the query name since it is the only item in the FROM
clause

So if I made all those changes correctly, you would end up with
Dim StrSQL as String

StrSQL = "SELECT CSR_Number, Option_Name " & _
", Nom_Date, [Date Of Nom Visit], .Pln_dwg_Rec" & _
", DateDiff(""d"",[Date Of Nom Visit],Now())-7 AS [Days Overdue]" & _
", Contractor_Name FROM Master_KPI_qry " & _
" WHERE Nom_Date Is Not Null AND Pln_dwg_Rec Is Null " & _
" AND DateDiff(""d"",[Date Of Nom Visit],Now())-7)>=0 " & _
" AND Contractor_Name= """ & [Forms]![company_filter_tbl]![Combo2] & """ " &
_
" ORDER BY Contractor_Name, CSR_Number"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..