what is wrong with this update query?

  • Thread starter Thread starter mcnews
  • Start date Start date
M

mcnews

UPDATE tblFollowUpData INNER JOIN vwPassenger ON tblFollowUpData.PLFId
= vwPassenger.PLFId SET tblFollowUpData.qstation = (SELECT qstation
FROM vwPassenger
WHERE Not isnull(vwPassenger.ConveyanceID) AND vwPassenger.PLFId =
tblFollowUpData.PLFId
AND (IsNull(tblFollowUpData.qstation) and Not
IsNull(vwPassenger.qstation)));


i get a 'Operation must use an updatebale query' error.

tia,
mcnewsxp
 
Would the following work for you?


UPDATE tblFollowUpData INNER JOIN vwPassenger
ON tblFollowUpData.PLFId= vwPassenger.PLFId
SET tblFollowUpData.qstation = vwPassenger.qStation

WHERE vwPassenger.ConveyanceID Is NOT Null
AND tblFollowUPDate.QStation is Null
AND vwPassenger.qStation is Not Null

Your query has the problem that the Subquery COULD return more than one
record and Access knows that and refuses to use the subquery.

Your other option would be to use the DLOOKUP function to return a value
(or one of the other VBA domain functions)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
John said:
Would the following work for you?


UPDATE tblFollowUpData INNER JOIN vwPassenger
ON tblFollowUpData.PLFId= vwPassenger.PLFId
SET tblFollowUpData.qstation = vwPassenger.qStation

WHERE vwPassenger.ConveyanceID Is NOT Null
AND tblFollowUPDate.QStation is Null
AND vwPassenger.qStation is Not Null

Your query has the problem that the Subquery COULD return more than one
record and Access knows that and refuses to use the subquery.

Your other option would be to use the DLOOKUP function to return a value
(or one of the other VBA domain functions)
Click to expand...
i get the same error.
i'd prefer not to use the dlookup because i may need to make thisa
stored proc on the sql server.
thanks much, tho.
 
Is vwPassenger a query or a view on the SQL server?

If it is a query, then post the SQL for it. If it is a view, then you
may need to find a workaround - such as a stored proc on the SQL server.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Is vwPassenger a query or a view on the SQL server?

If it is a query, then post the SQL for it. If it is a view, then you
may need to find a workaround - such as a stored proc on the SQL server.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Click to expand...

view.
must be the problem.
i wrote some code to do it.
thanks again.
 
Back
Top