SQL statement error when using select and update query

Question

I am trying to use a standard query which I got to work but now I need it to update a field in the table.

The below is my SQL but I keep getting the error: Syntax error in query expression

'FuelMaster.FleetNo = qryEndOdo.FleetNo
AND FuelMaster.FillOdo = qryEndOdo.FillOdo
AND FuelMaster.Date >= qryEndOdo.Date.

Below is my SQL:

UPDATE FuelMaster
SET FuelMaster.EndOdo = ((SELECT FuelMaster.ID, FuelMaster.Date, FuelMaster.FleetNo, FuelMaster.FillOdo, (SELECT TOP 1 Dupe.FillOdo
FROM FuelMaster AS Dupe
WHERE Dupe.FleetNo = FuelMaster.FleetNo
AND Dupe.Date >= FuelMaster.Date
AND Dupe.FillOdo > FuelMaster.FillOdo
ORDER BY Dupe.Date ASC) AS EndOdo
FROM FuelMaster))
WHERE EXISTS (FuelMaster.FleetNo = qryEndOdo.FleetNo
AND FuelMaster.FillOdo = qryEndOdo.FillOdo
AND FuelMaster.Date >= qryEndOdo.Date);

Answer 1

I tested UPDATE and get error "Operation must use an updatable query". Don't think can get what you want with single query. Options:

1. save correlated query object

SELECT ID, Nz((SELECT TOP 1 FillOdo FROM FuelMaster AS DUP 
    WHERE DUP.FleetNo=FuelMaster.FleetNo AND Dup.FillDate<FuelMaster.FillDate 
    ORDER BY FillDate DESC),0) AS BeginOdo
FROM FuelMaster;

and use it as source for DLookup() to update a field named BeginOdo (instead of EndOdo)

UPDATE FuelMaster SET FuelMaster.BeginOdo = DLookUp("BeginOdo","Query1","ID=" & [ID]);

2. populate a temp table with correlated query records and use table as source for UPDATE

3. use VBA and loop recordset object

4. don't update table, pull previous odometer reading via query when needed - already have the query to accomplish that