M
macjack48
I have a large query in Access.
I want to read it into Excel using my ADO.
I need to filter that Query with a WHERE statement.
I have a string variable ProjectNumber which happens to be my Sheet
Name
I want to filter on that.
Here is my SQL:
sSQL = "SELECT DISTINCT SampleSet.SetNo, Projects.Client,
Projects.Project_Name, Projects.Project_Number,
Projects.Project_Manager, SampleSet.Pour_Date, SampleSet.Sample,
SampleSet.Mold, SampleSet.Spec, SampleSet.Material, SampleSet.
[Concrete Class], SampleSet.Supplier, SampleSet.[Total Yards],
SampleSet.[Truck Number], SampleSet.[Batch Ticket Number], SampleSet.
[Batch Time], SampleSet.Slump, SampleSet.Air, SampleSet.[Mix Temp],
SampleSet.[Ambient Temp], SampleSet.[Time of Test], SampleSet.[Total
Number of Cylinders Cast], SampleSet.[Number of Field Cylinders Field
Cured], SampleSet.[Initial Field Cure Temp Range], SampleSet.Location,
SampleSet.Notes, qryAvgBySetNo.[_7_AVG], qryAvgBySetNo.[_7_PER],
qryAvgBySetNo.[_28_AVG], qryAvgBySetNo.[_28_PER], qryAvgBySetNo.
[_56_AVG], qryAvgBySetNo.[_56_PER], Query1.SerialNo, Query1.Expr1,
Query1.[Field Cure Days], Query1.Expr2, Query1.Cycle, Query1.[Weight,
gm], Query1.Diameter_1, Query1.Diameter_2, Query1.Length,
Query1.Expr3, Query1.Expr4, Query1.[Break, lbs]," & _
" " & "Query1.Expr5 , Query1.Expr6, Query1.Failure, Query1.Notes,
Query1.Expr7, Query1.AVG_7, Query1.AVG_28, Query1.AVG_56 FROM
Projects, (SampleSet INNER JOIN qryAvgBySetNo ON SampleSet.SetNo =
qryAvgBySetNo.SetNo) INNER JOIN Query1 ON SampleSet.SetNo =
Query1.SetNo;" & _
" " & "WHERE Projects.Project_Number=28547;"
The last line is where I am not sure how to repalce the 28547 with my
Variable ProjectNumber
" " & "WHERE Projects.Project_Number=ProjectNumber;"
How should this syntax read?
I know it is a string eventhough I called it ProjectNumber.
The first 2 parts of the SQL work ok.
I want to read it into Excel using my ADO.
I need to filter that Query with a WHERE statement.
I have a string variable ProjectNumber which happens to be my Sheet
Name
I want to filter on that.
Here is my SQL:
sSQL = "SELECT DISTINCT SampleSet.SetNo, Projects.Client,
Projects.Project_Name, Projects.Project_Number,
Projects.Project_Manager, SampleSet.Pour_Date, SampleSet.Sample,
SampleSet.Mold, SampleSet.Spec, SampleSet.Material, SampleSet.
[Concrete Class], SampleSet.Supplier, SampleSet.[Total Yards],
SampleSet.[Truck Number], SampleSet.[Batch Ticket Number], SampleSet.
[Batch Time], SampleSet.Slump, SampleSet.Air, SampleSet.[Mix Temp],
SampleSet.[Ambient Temp], SampleSet.[Time of Test], SampleSet.[Total
Number of Cylinders Cast], SampleSet.[Number of Field Cylinders Field
Cured], SampleSet.[Initial Field Cure Temp Range], SampleSet.Location,
SampleSet.Notes, qryAvgBySetNo.[_7_AVG], qryAvgBySetNo.[_7_PER],
qryAvgBySetNo.[_28_AVG], qryAvgBySetNo.[_28_PER], qryAvgBySetNo.
[_56_AVG], qryAvgBySetNo.[_56_PER], Query1.SerialNo, Query1.Expr1,
Query1.[Field Cure Days], Query1.Expr2, Query1.Cycle, Query1.[Weight,
gm], Query1.Diameter_1, Query1.Diameter_2, Query1.Length,
Query1.Expr3, Query1.Expr4, Query1.[Break, lbs]," & _
" " & "Query1.Expr5 , Query1.Expr6, Query1.Failure, Query1.Notes,
Query1.Expr7, Query1.AVG_7, Query1.AVG_28, Query1.AVG_56 FROM
Projects, (SampleSet INNER JOIN qryAvgBySetNo ON SampleSet.SetNo =
qryAvgBySetNo.SetNo) INNER JOIN Query1 ON SampleSet.SetNo =
Query1.SetNo;" & _
" " & "WHERE Projects.Project_Number=28547;"
The last line is where I am not sure how to repalce the 28547 with my
Variable ProjectNumber
" " & "WHERE Projects.Project_Number=ProjectNumber;"
How should this syntax read?
I know it is a string eventhough I called it ProjectNumber.
The first 2 parts of the SQL work ok.