Whats wrong with my SQL?

[macroapa]

Hi, I have the following SQL which runs fine in MSAccess and returns
results:

SELECT tblContractedHrs.Team, tblTouches08ProdAPA.stepname, Sum
(tblTouches08ProdAPA.duration) AS SumOfduration
FROM tblTouches08ProdAPA INNER JOIN tblContractedHrs ON
(tblTouches08ProdAPA.TouchStart = tblContractedHrs.WorkDate) AND
(tblTouches08ProdAPA.level7 = tblContractedHrs.WFIName)
WHERE (((tblTouches08ProdAPA.level7)="Ray, Ray") AND
((tblTouches08ProdAPA.TouchStart)>#1/1/2008# And
(tblTouches08ProdAPA.TouchStart)<#1/1/2009#))
GROUP BY tblContractedHrs.Team, tblTouches08ProdAPA.stepname;


I've then taken this and added it to excel VBA to extract the data
into excel (ultimately the name and dates will be variables as opposed
to hard coded), but I keep getting an error in the sql saying that
there are too few arguments.

I;ve tried it numerous ways and also using MSQuery in excel to do it,
but to no avail. So why does it work in Access and not excel?

Thanks for any help..... I'm banging my head against a brick wall at
the momeny.

Function test2()
Dim vConnection As New ADODB.Connection
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
Set vConnection = New ADODB.Connection



Dim db As String

db = "data source=J:\Protection-Customer-Service\MI Team\WFI History
Table\WFIHistory.mdb;"

vConnection.ConnectionString = db & "Provider=Microsoft.Jet.OLEDB.
4.0;"
vConnection.Open

Dim xname As String
xname = "Ray, Ray"

sql1 = "SELECT tblContractedHrs.Team, tblTouches08ProdAPA.stepname,
Sum(tblTouches08ProdAPA.duration) AS SumOfduration "
sql2 = "FROM tblTouches08ProdAPA INNER JOIN tblContractedHrs ON
(tblTouches08ProdAPA.TouchStart = tblContractedHrs.WorkDate) AND
(tblTouches08ProdAPA.level7 = tblContractedHrs.WFIName) "
sql3 = "WHERE (((tblTouches08ProdAPA.level7)=xName) AND
((tblTouches08ProdAPA.TouchStart) Between #01/01/2008# And
#01/01/2009#)); "
sql4 = "GROUP BY tblContractedHrs.Team, tblTouches08ProdAPA.stepname;"
sql = sql1 & sql2 & sql3 & sql4

With rsPubs
.ActiveConnection = vConnection
.Open sql

Sheet1.Range("A1").CopyFromRecordset rsPubs
.Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set vConnection = Nothing

End Function

 

[Dirk Goldgar]

Hi, I have the following SQL which runs fine in MSAccess and returns
results:

SELECT tblContractedHrs.Team, tblTouches08ProdAPA.stepname, Sum
(tblTouches08ProdAPA.duration) AS SumOfduration
FROM tblTouches08ProdAPA INNER JOIN tblContractedHrs ON
(tblTouches08ProdAPA.TouchStart = tblContractedHrs.WorkDate) AND
(tblTouches08ProdAPA.level7 = tblContractedHrs.WFIName)
WHERE (((tblTouches08ProdAPA.level7)="Ray, Ray") AND
((tblTouches08ProdAPA.TouchStart)>#1/1/2008# And
(tblTouches08ProdAPA.TouchStart)<#1/1/2009#))
GROUP BY tblContractedHrs.Team, tblTouches08ProdAPA.stepname;


I've then taken this and added it to excel VBA to extract the data
into excel (ultimately the name and dates will be variables as opposed
to hard coded), but I keep getting an error in the sql saying that
there are too few arguments.

I;ve tried it numerous ways and also using MSQuery in excel to do it,
but to no avail. So why does it work in Access and not excel?

Thanks for any help..... I'm banging my head against a brick wall at
the momeny.

Function test2()
Dim vConnection As New ADODB.Connection
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
Set vConnection = New ADODB.Connection



Dim db As String

db = "data source=J:\Protection-Customer-Service\MI Team\WFI History
Table\WFIHistory.mdb;"

vConnection.ConnectionString = db & "Provider=Microsoft.Jet.OLEDB.
4.0;"
vConnection.Open

Dim xname As String
xname = "Ray, Ray"

sql1 = "SELECT tblContractedHrs.Team, tblTouches08ProdAPA.stepname,
Sum(tblTouches08ProdAPA.duration) AS SumOfduration "
sql2 = "FROM tblTouches08ProdAPA INNER JOIN tblContractedHrs ON
(tblTouches08ProdAPA.TouchStart = tblContractedHrs.WorkDate) AND
(tblTouches08ProdAPA.level7 = tblContractedHrs.WFIName) "
sql3 = "WHERE (((tblTouches08ProdAPA.level7)=xName) AND
((tblTouches08ProdAPA.TouchStart) Between #01/01/2008# And
#01/01/2009#)); "
sql4 = "GROUP BY tblContractedHrs.Team, tblTouches08ProdAPA.stepname;"
sql = sql1 & sql2 & sql3 & sql4

With rsPubs
.ActiveConnection = vConnection
.Open sql

Sheet1.Range("A1").CopyFromRecordset rsPubs
.Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set vConnection = Nothing

End Function

1. You're trying to use the variable xName directly in the literal SQL
string, and that doesn't work. When the database engine processes that SQL
statement, it knows nothing about your variable. You need to embed the
literal value of the variable in the SQL string, surrounded by quotes.

2. You've got an early semicolon terminating your SQL statement before the
GROUP BY clause.

Try this:

'------ start of revised code -- you must fix the line wrapping ------
sql1 = "SELECT tblContractedHrs.Team, tblTouches08ProdAPA.stepname,
Sum(tblTouches08ProdAPA.duration) AS SumOfduration "

sql2 = "FROM tblTouches08ProdAPA INNER JOIN tblContractedHrs ON
(tblTouches08ProdAPA.TouchStart = tblContractedHrs.WorkDate) AND
(tblTouches08ProdAPA.level7 = tblContractedHrs.WFIName) "

sql3 = _
"WHERE (((tblTouches08ProdAPA.level7)=" & _
Chr(34) & xName & Chr(34) & _
") AND ((tblTouches08ProdAPA.TouchStart) " & _
"Between #01/01/2008# And #01/01/2009#)) "

sql4 = "GROUP BY tblContractedHrs.Team, tblTouches08ProdAPA.stepname;"

sql = sql1 & sql2 & sql3 & sql4
'------ end of revised code ------

 

[macroapa]

Thank you so much..... the 2nd error was just me copying and pasting
wrong in here.

That works perfectly. Now you've explained it, it makes sense, but I
would never have figured it out myself.

Thank you again.

Steve