DSUM()

[Andy]

Hi
I Have following select Query in Query table

PreciousQtyselect Sum([SC2Day'sQty] FROM DWPRecord AS A Where
A.[ItemNo]<=DWPRecord.[ItemNo] AND A.[SCNo]=DWPRecord.[SCNo] AND
[A].[Date]<=[DWPRecord].[Date]) This query works perfect. but report does
not allow me to have multiple level grouping!!!!!

So I use PreviousQtySUM("[SC2Day'sQty]","DWPRecord","[ItemNo]<="
&DWPRecord.[ItemID] AND "[SCNo]=" &DWPRecord.[SCNo] AND " [Date]<=" &
DWPRecord.[Date]) which should give same results as select Query.

But it doesn't !!!!!! gives overall total of SC2Day'sQty field . ( Not Item
wise,SubCode wise and Date wise)

Where am I doing Mistake in DSUM() ?

ANDY

 

[Allen Browne]

Several possiblities here, Andy.

1. ANDs are in the wrong place, and ampersands are missing.
2. Date is not delimited with #, and is subject to regional formatting
issues.
3. Null values could mess up the Criteria.
4. Field named Date.

Try:

DSum("[SC2Day'sQty]", "DWPRecord",
"([ItemNo] <= " & Nz(DWPRecord.[ItemID], 0) &
") AND ([SCNo]=" & Nz(DWPRecord.[SCNo], 0) &
") AND ([Date] <= " & Format(Nz(DWPRecord.[Date], #1/1/2999#),
"\#mm\/dd\/yyyy\#) & ")")

That assumes that ItemID and SCNo are fields of type Number (not Text), and
Date is a field of type Date/Time.

In some contexts, Access will misunderstand Date to be the system date, so
you might want to change the field name whereever it is used. Before you do,
make sure Name AutoCorrect is off.

Other field names to avoid include Name, Year, Day, Month, and so on. Full
list:
http://allenbrowne.com/AppIssueBadWord.html

 

[John Spencer]

PreciousQtyselect Sum([SC2Day'sQty] FROM DWPRecord AS A Where
A.[ItemNo]<=DWPRecord.[ItemNo] AND A.[SCNo]=DWPRecord.[SCNo] AND
[A].[Date]<=[DWPRecord].[Date]) This query works perfect. but report does
not allow me to have multiple level grouping!!!!!

So I use PreviousQtySUM("[SC2Day'sQty]","DWPRecord","[ItemNo]<="
&DWPRecord.[ItemID] AND "[SCNo]=" &DWPRecord.[SCNo] AND " [Date]<=" &
DWPRecord.[Date]) which should give same results as select Query.


In the Subquery you refer to ItemNo and in the DSum your refer to
ItemID. Is that a typo or are you using different fields?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Andy]

Hi John,
Yes It was typo. ItemNo is the field which I ment not the ItemID
Andy. I am trying with what Allen suggested and see what happens. If i'll
have prob. I'll reask this question.

Thanks for helping me'.

Andy

PreciousQtyselect Sum([SC2Day'sQty] FROM DWPRecord AS A Where
A.[ItemNo]<=DWPRecord.[ItemNo] AND A.[SCNo]=DWPRecord.[SCNo] AND
[A].[Date]<=[DWPRecord].[Date]) This query works perfect. but report does
not allow me to have multiple level grouping!!!!!

So I use PreviousQtySUM("[SC2Day'sQty]","DWPRecord","[ItemNo]<="
&DWPRecord.[ItemID] AND "[SCNo]=" &DWPRecord.[SCNo] AND " [Date]<=" &
DWPRecord.[Date]) which should give same results as select Query.


In the Subquery you refer to ItemNo and in the DSum your refer to
ItemID. Is that a typo or are you using different fields?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi
I Have following select Query in Query table

PreciousQtyselect Sum([SC2Day'sQty] FROM DWPRecord AS A Where
A.[ItemNo]<=DWPRecord.[ItemNo] AND A.[SCNo]=DWPRecord.[SCNo] AND
[A].[Date]<=[DWPRecord].[Date]) This query works perfect. but report does
not allow me to have multiple level grouping!!!!!

So I use PreviousQtySUM("[SC2Day'sQty]","DWPRecord","[ItemNo]<="
&DWPRecord.[ItemID] AND "[SCNo]=" &DWPRecord.[SCNo] AND " [Date]<=" &
DWPRecord.[Date]) which should give same results as select Query.

But it doesn't !!!!!! gives overall total of SC2Day'sQty field . ( Not Item
wise,SubCode wise and Date wise)

Where am I doing Mistake in DSUM() ?

ANDY