| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Syntax
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL Syntax
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. " wrote: 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL Syntax
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 wrote in message ... : : 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL Syntax
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" wrote: 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. " wrote: 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL Syntax
On Nov 7, 1:39*pm, Michael wrote:
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. " wrote: 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.- Hide quoted text - - Show quoted text - I get the error that characters are found at the end of the SQL statement |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SQL Syntax
wrote in message ... On Nov 7, 1:39 pm, Michael wrote: 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 - I get the error that characters are found at the end of the SQL statement Drop the semicolon at the end. Tim |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A bit of Syntax help please... | Excel Programming | |||
| Need syntax help! | Excel Programming | |||
| VBA syntax | Excel Discussion (Misc queries) | |||
| Syntax | Excel Programming | |||
| syntax for a sub ( ) | Excel Programming | |||
Linear Mode
