UPDATE Query Problem - Must be an updateable query

W

W Dean

Hi,

I'm trying to run the following query where a field in the main table
SessionOrders is updated with a value from another table ServiceLevels but
Access reports an error 'Must be updateable query'. I have a vague
recollection that this is something to do with access not allowing aggregate
queries in a update statement but how do i write this sql statement so it
works?

UPDATE SessionOrders
SET
SessionOrders.CollectionDate = IIf(IsNull([aCollectionDay]) Or
IsNull([aCollectionMonth]) Or
IsNull([aCollectionYear]),Null,DateSerial([aCollectionYear],[aCollectionMont
h],[aCollectionDay])),
SessionOrders.CollectionTime = [aCollectionTime],
SessionOrders.ServiceLevel = [aServiceLevel],
SessionOrders.AdditionalCharge = (SELECT ServiceLevels.AdditionalCharge
FROM ServiceLevels WHERE ServiceLevels.ID = [aServiceLevel]),
SessionOrders.CollectionInstructions = [aCollectionInstructions],
SessionOrders.Updated = Now()
WHERE
(SessionOrders.ID=[aID]);


W Dean
 
Top