SQL Syntax

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.
 
M

Michael

Assuming the sheet you want is the first sheet in your workbook,
myvar=myvar = Sheets(1).Name

Then substitute your variable:

" " & "WHERE Projects.Project_Number="& myvar & ";"
 
P

PaulD

If project number is a number use
& "WHERE Projects.Project_Number=" & ProjectNumber & ";"

If it's a string use
& "WHERE Projects.Project_Number='" & ProjectNumber & "';"

strings must be enclosed in quotes either " " or ' ', I prefer ' since they
are easier to see when enclosed in "
Paul D


:
: 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.
 
M

Michael

Sorry a mistyping myvar=Sheets(1).Name
not what I pasted before.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




Michael said:
Assuming the sheet you want is the first sheet in your workbook,
myvar=myvar = Sheets(1).Name

Then substitute your variable:

" " & "WHERE Projects.Project_Number="& myvar & ";"

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




[email protected] said:
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.
Click to expand...
Click to expand...
 
M

macjack48

Assuming the sheet you want is the first sheet in your workbook,
myvar=myvar = Sheets(1).Name

Then substitute your variable:

" " & "WHERE Projects.Project_Number="& myvar & ";"

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.



[email protected] said:
I have a large query in Access.
I want to read it into Excel using my ADO.
Click to expand...
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:
Click to expand...
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;"
Click to expand...
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.
Click to expand...
The first 2 parts of the SQL work ok.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I get the error that characters are found at the end of the SQL
statement
 
T

Tim Williams

Assuming the sheet you want is the first sheet in your workbook,
myvar=myvar = Sheets(1).Name

Then substitute your variable:

" " & "WHERE Projects.Project_Number="& myvar & ";"

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.



- Show quoted text -
Click to expand...
I get the error that characters are found at the end of the SQL
statement
Click to expand...

Drop the semicolon at the end.

Tim
 
Top