SQL syntax error 3

[merlynsdad]

I have a query qryTotalRdyTm with two fields, SEID and ReadyTime, on which I am summing. I have a table called tblSEIDtoExt_Num with a date/time field called TotalRdyTm. I'm trying to move the result of the query to that field using the following SQL (in VBA):

strSQL = "UPDATE tblSEIDtoExt_Num " _
& "SELECT Sum(tblSEIDtoExt_Num.ReadyTime) AS SumOfReadyTime " _
& "FROM qryTotalRdyTm " _
& "SET tblSEIDtoExt_Num.TotalRdyTm = qryTotalRdyTm.SumOfReadyTime " _
& "WHERE SEID= '" & strSEID & "';"

I'm getting a syntax error and can't find the problem.

If the square peg won't fit in the round hole, sand off the corners.

 

[dhookom]

I assume you have a legitimate reason to store a value that can be calculated at any time.

Any query with a Sum() will not be updateable. You might need either code to edit a record in a recordset or use DSum() to update a field.

strSQL = "UPDATE tblSEIDtoExt_Num " & _
  "SET TotalRdyTm = DSum('ReadyTime','tblSEIDToExt_Num','SEID='" & _
  strSEID & "')"
  debug.Print strSQL  [green]'Find out the full SQL[/green]

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

try something like this
put this string in to SqlStr

update tblSEIDtoExt_Num 
inner join (
            SELECT Sum(tblSEIDtoExt_Num.ReadyTime) 
            AS SumOfReadyTime 
            FROM qryTotalRdyTm 
            Group by strSEID
            Where strSEID =" & strSEID & _
            ) As SOfRT
on SOfRT.strSEID =tblSEIDtoExt_Num.strSEID 
SET tblSEIDtoExt_Num.TotalRdyTm = SOfRT.SumOfReadyTime

 

[dhookom]

PWise,
I think this is still not updateable because of the totals query in the subquery. Other flavors of SQL might allow this but Access typically doesn't.

I tried something similar in Northwind just to be sure. I added a ProductCount field in the Categories table. I wanted to update the field to the number of products in the category. This SQL gets me the count:

SELECT Products.CategoryID, Count(Products.ProductID) AS CountOfProductID
FROM Products
GROUP BY Products.CategoryID

so I thought this might match your update syntax:

UPDATE Categories INNER JOIN (SELECT Products.CategoryID, Count(Products.ProductID) AS CountOfProductID
FROM Products
GROUP BY Products.CategoryID) As PrdCnt
ON Categories.CategoryID = PrdCnt.CategoryID
SET ProductCount = PrdCnt.CountOfProductID;

I got the error message:
Operation must use an updateable query
This query does work:

UPDATE Categories SET Categories.ProductCount = DCount("*","Products","CategoryID =" & [CategoryID]);

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

Duane
you are right as usual

This works in SQLServer

have another Star

 

[dhookom]

I wish we had some of the SQL Server functionality available in Access such as your syntax, CTE, and many others. Of course, SQL Server should add a simple crosstab like Access.

Duane
Hook'D on Access
MS Access MVP

 

[merlynsdad]

For someone who's new to Access VBA & SQL (background is Excel & Word VBA) it's certainly confusing! I do appreciate the quick responses from you guys, though it seems I have a new question for every response.

If the square peg won't fit in the round hole, sand off the corners.