DSUM()

[Andy]

Hello,
Few days ago I asked question regarding DSUM() and as per answers I
received I did correction but DSum is still not working!!!!
So I am putting here my SQL Query so if someone can find my mistake.
In this Query I have some Sub Queries which works ok but I 'D like to
replace it wit DSUM().
SELECT DWPRecord.Date, DWPRecord.ItemNo, DWPRecord.SCNo,
SubCodeList.Station, ItemList.ItemName, ItemList.InspLevel,
ItemList.Milestone, ItemList.Unit, ItemList.TypeQty, DWPRecord.[I-Hwy],
DWPRecord.[I-Dir], DWPRecord.[I-Lane], DWPRecord.[I-Lift],
DWPRecord.[I-FromSta], DWPRecord.[I-ToSta],
[ItemList].[TenderQty]+[ItemList].[ChangedQty] AS [I-FTenderQty],
DWPRecord.[I-2DayQty], DWPRecord.[I-2DayPayQty], DWPRecord.JMFNo,
DWPRecord.[I-BRD], DWPRecord.[I-Lot], DWPRecord.[I-SLot],
DWPRecord.[I-WDescription], DWPRecord.sta1, DWPRecord.W1, DWPRecord.D1,
DWPRecord.sta2, DWPRecord.W2, DWPRecord.D2, DWPRecord.sta3, DWPRecord.W3,
DWPRecord.D3, DWPRecord.sta4, DWPRecord.W4, DWPRecord.D4, DWPRecord.sta5,
DWPRecord.W5, DWPRecord.D5, DWPRecord.sta6, DWPRecord.W6, DWPRecord.D6,
IIf(IsNull([W1])+IsNull([W2])+IsNull([W3])+IsNull([W4])+IsNull([W5])+IsNull([W6]),"
",(Nz([W1],0)+Nz([W2],0)+Nz([W3],0)+Nz([W4],0)+Nz([W5],0)+Nz([W6],0))/Abs(6+IsNull([W1])+IsNull([W2])+IsNull([W3])+IsNull([W4])+IsNull([W5])+IsNull([W6])))
AS AvgW,
IIf(0=[D1]<>0+[D2]<>0+[D3]<>0+[D4]<>0+[D5]<>0+[D6]<>0,([D1]+[D2]+[D3]+[D4]+[D5]+[D6])/Abs(6+IsNull([D1])+IsNull([D2])+IsNull([D3])+IsNull([D4])+IsNull([D5])+IsNull([D6])))
AS AvgD, DWPRecord.[P-W], DWPRecord.[P-D], DWPRecord.[I-Complete],
DWPRecord.SCFromSta, DWPRecord.SCToSta, DWPRecord.SCWidth, DWPRecord.SCTh,
DWPRecord.[SCToDay'sQty], (SELECT SUM([SCToDay'sQty]) FROM DWPRecord AS A
WHERE A.[ItemNo]=DWPRecord.[ItemNo] AND A.[Date]=DWPRecord.[Date]) AS
ItemTotalQty2Day, (Select Sum ([SCToDay'sQty]) FROM DWPRecord AS A WHERE
A.[ItemNo]<=DWPRecord.[ItemNo] AND A.[SCNo]=DWPRecord.[SCNo] AND
A.[Date]<DWPRecord.[Date]) AS SCPreviousQty, (Select Sum ([SCToDay'sQty])
FROM DWPRecord AS A WHERE A.[ItemNo]<=DWPRecord.[ItemNo] AND
A.[SCNo]=DWPRecord.[SCNo] AND A.[Date]<=DWPRecord.[Date]) AS SC2DateQty,
DWPRecord.[SCToDay'sPayQty], (Select Sum ([SCToDay'sPayQty]) FROM DWPRecord
AS A WHERE A.[ItemNo]<=DWPRecord.[ItemNo] AND A.[SCNo]=DWPRecord.[SCNo] AND
A.[Date]<DWPRecord.[Date]) AS SPreviouslyPaidQty, (Select Sum
([SCToDay'sPayQty]) FROM DWPRecord AS A WHERE A.[ItemNo]<=DWPRecord.[ItemNo]
AND A.[SCNo]=DWPRecord.[SCNo] AND A.[Date]<=DWPRecord.[Date]) AS
SCQtyPaid2Date, [SC2DateQty]-[SCQtyPaid2Date] AS SCQty2Pay,
DWPRecord.SCWorkDescription, DWPRecord.CPR, DWPRecord.SCPWidth,
DWPRecord.SCPTH, DWPRecord.[SC-Complete],
DSum("[SCToDay'sQty]","DWPRecord","([ItemNo]<=" & Nz([DWPRecord].[ItemNo],0)
& ") And ([SCNo]=" & Nz([DWPRecord].[SCNo],0) & ") And ([Date]<" &
Format(Nz([DWPRecord].[Date],#1/1/9999#),"\#mm\/dd\/yyyy") & ")") AS Trial
FROM SubCodeList INNER JOIN (ItemList INNER JOIN DWPRecord ON
ItemList.ItemID = DWPRecord.ItemNo) ON SubCodeList.SCID = DWPRecord.SCNo
GROUP BY DWPRecord.Date, DWPRecord.ItemNo, DWPRecord.SCNo,
SubCodeList.Station, ItemList.ItemName, ItemList.InspLevel,
ItemList.Milestone, ItemList.Unit, ItemList.TypeQty, DWPRecord.[I-Hwy],
DWPRecord.[I-Dir], DWPRecord.[I-Lane], DWPRecord.[I-Lift],
DWPRecord.[I-FromSta], DWPRecord.[I-ToSta],
[ItemList].[TenderQty]+[ItemList].[ChangedQty], DWPRecord.[I-2DayQty],
DWPRecord.[I-2DayPayQty], DWPRecord.JMFNo, DWPRecord.[I-BRD],
DWPRecord.[I-Lot], DWPRecord.[I-SLot], DWPRecord.[I-WDescription],
DWPRecord.sta1, DWPRecord.W1, DWPRecord.D1, DWPRecord.sta2, DWPRecord.W2,
DWPRecord.D2, DWPRecord.sta3, DWPRecord.W3, DWPRecord.D3, DWPRecord.sta4,
DWPRecord.W4, DWPRecord.D4, DWPRecord.sta5, DWPRecord.W5, DWPRecord.D5,
DWPRecord.sta6, DWPRecord.W6, DWPRecord.D6,
IIf(IsNull([W1])+IsNull([W2])+IsNull([W3])+IsNull([W4])+IsNull([W5])+IsNull([W6]),"
",(Nz([W1],0)+Nz([W2],0)+Nz([W3],0)+Nz([W4],0)+Nz([W5],0)+Nz([W6],0))/Abs(6+IsNull([W1])+IsNull([W2])+IsNull([W3])+IsNull([W4])+IsNull([W5])+IsNull([W6]))),
IIf(0=[D1]<>0+[D2]<>0+[D3]<>0+[D4]<>0+[D5]<>0+[D6]<>0,([D1]+[D2]+[D3]+[D4]+[D5]+[D6])/Abs(6+IsNull([D1])+IsNull([D2])+IsNull([D3])+IsNull([D4])+IsNull([D5])+IsNull([D6]))),
DWPRecord.[P-W], DWPRecord.[P-D], DWPRecord.[I-Complete],
DWPRecord.SCFromSta, DWPRecord.SCToSta, DWPRecord.SCWidth, DWPRecord.SCTh,
DWPRecord.[SCToDay'sQty], DWPRecord.[SCToDay'sPayQty],
DWPRecord.SCWorkDescription, DWPRecord.CPR, DWPRecord.SCPWidth,
DWPRecord.SCPTH, DWPRecord.[SC-Complete]
HAVING (((DWPRecord.Date)=DMax("[Date]","DWPRecord")));


Andy