Update Query Problem - Field too Small

[mikebaldam]

Hi,

I have a problem tyring to use my SQL in an update query.. It works fine as a select query but when I try to use it as an update I get trhe error message

Field too small to small to insert the amount of data. Try inserting or Pasting Less

I'm not sure of any other way around this. The query a non-updateable due to expressions in it, or I would try to change a field to alter the JobPrice field.

I'm trying to update a Currency value (based on an expression) from a query to a table (with the field also set up as a currency field).

Here's the select SQL I'm using...

SELECT TblAgent.AgentID, TblAgent.Agent, tblAddress.ID, tblAddress.Address1, tblAddress.Address2, tblAddress.Address3, tblAddress.Address4, tblAddress.Postcode, TblAgent.Office, TblBoardJobs.BoardJobID, TblBoardJobs.CompletedDate, TblBoardJobs.Remove, TblBoardJobs.ForSaleSingle, TblBoardJobs.ForSaleDouble, TblBoardJobs.VBAO, TblBoardJobs.UnderOffer, TblBoardJobs.SoleAgent, TblBoardJobs.SaleAgreed, TblBoardJobs.ArrowSlip, TblBoardJobs.BuildingPlot, TblBoardJobs.Pointer, TblBoardJobs.JourneyCharge, TblBoardJobs.Refix, TblBoardJobs.LetBy, TblBoardJobs.ToLet, TblBoardJobs.MakeForSale, TblBoardJobs.SSTC, TblBoardJobs.AddSlips, TblBoardJobs.Move, TblBoardJobs.SPSLtick, TblBoardJobs.SPSL, TblBoardJobs.ByAuction, TblBoardJobs.Amount, IIf([TblBoardJobs]![Remove],[TblPrice]![Remove],0) AS RemovePrice, IIf([TblBoardJobs]![ForSaleSingle],[TblPrice]![ForSaleSingle],0) AS ForSaleSinglePrice, IIf([TblBoardJobs]![ForSaleDouble],[TblPrice]![ForSaleDouble],0) AS ForSaleDoublePrice, IIf([TblBoardJobs]![VBAO],[TblPrice]![VBAO],0) AS VBAOPrice, IIf([TblBoardJobs]![UnderOffer],[TblPrice]![UnderOffer],0) AS UnderOfferPrice, IIf([TblBoardJobs]![SoleAgent],[TblPrice]![SoleAgent],0) AS SoleAgentPrice, IIf([TblBoardJobs]![SaleAgreed],[TblPrice]![SaleAgreed],0) AS SaleAgreedPrice, IIf([TblBoardJobs]![ArrowSlip],[TblPrice]![ArrowSlip],0) AS ArrowSlipPrice, IIf([TblBoardJobs]![BuildingPlot],[TblPrice]![BuildingPlot],0) AS BuildingPlotPrice, IIf([TblBoardJobs]![Pointer],[TblPrice]![Pointer],0) AS PointerPrice, IIf([TblBoardJobs]![JourneyCharge],[TblPrice]![JourneyCharge],0) AS JourneyChargePrice, IIf([TblBoardJobs]![Refix],[TblPrice]![Refix],0) AS RefixPrice, IIf([TblBoardJobs]![LetBy],[TblPrice]![LetBy],0) AS LetByPrice, IIf([TblBoardJobs]![ToLet],[TblPrice]![ToLet],0) AS ToLetPrice, IIf([TblBoardJobs]![MakeForSale],[TblPrice]![MakeForSale],0) AS MakeForSalePrice, IIf([TblBoardJobs]![SSTC],[TblPrice]![SSTC],0) AS SSTCPrice, IIf([TblBoardJobs]![AddSlips],[TblPrice]![AddSlips],0) AS AddSlipsPrice, IIf([TblBoardJobs]![Move],[TblPrice]![Move],0) AS MovePrice, IIf([TblBoardJobs]![SPSLtick],[TblPrice]![SPSLtick],0) AS SPSLPrice, IIf([TblBoardJobs]![ByAuction],[TblPrice]![ByAuction],0) AS ByAuctionPrice, IIf([TblBoardJobs]![Amount],[TblBoardJobs]![Amount],0) AS OtherPrice, TblBoardJobs.[RemovePrice]+[ForSaleSinglePrice]+[ForSaleDoublePrice]+[VBAOPrice]+[UnderOfferPrice]+[SoleAgentPrice]+[SaleAgreedPrice]+[ArrowSlipPrice]+[BuildingPlotPrice]+[PointerPrice]+[JourneyChargePrice]+[RefixPrice]+[LetByPrice]+[ToLetPrice]+[MakeForSalePrice]+[SSTCPrice]+[AddSlipsPrice]+[SPSLPrice]+[MovePrice]+[ByAuctionPrice]+[OtherPrice] AS JobPrice,
FROM tblAddress INNER JOIN ((TblPrice RIGHT JOIN TblAgent ON TblPrice.Pricing = TblAgent.Pricing) INNER JOIN TblBoardJobs ON TblAgent.AgentID = TblBoardJobs.AgentID) ON tblAddress.ID = TblBoardJobs.AddressID;

Hope someone can give me a hand....

Cheers

Mike