Abs(sum()) or DCount()

[AnhCVL via AccessMonster.com]

Hi all,

I have a data table name "main log table" and it contain couple fields, one
of them is a text field name "zquantity" which contained the numeric value
such as 10, 5, 20 depend on each record value. I want to perform calculation
for the total of all records with certain conditions ( let say the sum of
10+5+20), I am able to get it to work using Ab(sum)) when I put in in the
report footer. due to the lay out, I had to move the item to the page footer,
which generate #Error message on the display text field. I've try to used
Dcount and count but did not know the syntag. below is one of the statement
I tried:

=DCount(Abs(sum("zquantity","[Main Log Table]","[Type]='Letter' AND [Sig_Date]
is Not null")))

The table contained 3 categories, which is Letter, Books, and Other. I want
to calculate the total of each categories base on a field name "Sig_Date" by
evaluate if it is null or not null from the table name "main log table". let
say 30 letters was signed on the first row and 15 letter was sign on the
second row, the "Sig_date" of these rows will be not null, I want to evaluate
the calculation to get a sum of these 2 rows and display it on the page
footer, is it possible? Thanks

Mark

 

[Jeff Boyce]

If you want the total of values in the field, why would you use DCount()
(which counts the number of records)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[AnhCVL via AccessMonster.com]

I tried to used Abs(sum()) but it generate errors, since I tried the Dcount
and it work when counting record hen I put it at page footer, so I just tried
to use it in conjunction with Abs(sum()), any suggestion on this? Thanks

Mark

If you want the total of values in the field, why would you use DCount()
(which counts the number of records)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,

[quoted text clipped - 29 lines]

Mark

 

[John Spencer (MVP)]

" I had to move the item to the page footer"

I believe that the above is part of your problem. Access does not handle this
type of calculation in a page footer very well, if at all.

This expression is definitely not correct.
=DCount(Abs(sum("zquantity","[Main Log Table]","[Type]='Letter' AND [Sig_Date]
is Not null")))

Perhaps you want something along the lines of the following.
DCount("*","[Main Log Table]","[Type]='Letter' AND [Sig_Date] is Not null")

Or perhaps you want
DSum("zQuantity","[Main Log Table]","[Type]='Letter' AND Sig_Date is Not null")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[AnhCVL via AccessMonster.com]

Hi John,

Thanks for the tips. The code:

DSum("zQuantity","[Main Log Table]","[Type]='Letter' AND Sig_Date is Not
null")

works very good. I have another question thought, let say I have 2 code
statements for 2 display text boxes as below:

Display text box name Text10:
=DSum("zQuantity","[Main Log Table]","[Type]='Letter' AND Sig_Date is Not
null")

and
Display text box name Text11:
=DSum("zQuantity","[Main Log Table]","[Type]='Letter' AND Sig_Date is null")

when I have 15 ( Sum value of display text10) for the quantiy with signature
( [Sig_Date] is not null) and nothing/blank (Sum Value of display Text11) for
the unsigned ([Sig_Date] is null), when I try to add up these 2 field and
display on Text13 by using the statement below:
=Val(Text10)+Val(Text11)
as well as
=Cint(Text10)+Cint(Text11)

but both resulted in #ERROR message. Is there a way to force the display of
Text11 to 0(Zero) or to ignore that blank and treat it as 0 for the
calculation?

Thanks
Mark

" I had to move the item to the page footer"

I believe that the above is part of your problem. Access does not handle this
type of calculation in a page footer very well, if at all.

This expression is definitely not correct.
=DCount(Abs(sum("zquantity","[Main Log Table]","[Type]='Letter' AND [Sig_Date]
is Not null")))

Perhaps you want something along the lines of the following.
DCount("*","[Main Log Table]","[Type]='Letter' AND [Sig_Date] is Not null")

Or perhaps you want
DSum("zQuantity","[Main Log Table]","[Type]='Letter' AND Sig_Date is Not null")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi all,

[quoted text clipped - 20 lines]

Mark

 

[AnhCVL via AccessMonster.com]

Also,

I've tried the Nz function as below:

=Nz(text64,0)+Nz(text79,0)+Nz(text85,0)
=Nz(Val([text64]),0)+Nz(Val([text79]),0)+Nz(Val([text85]),0)

It wuold generate the same error, but this statement :

=Nz(text64,0)+Nz(text79,0)+Nz(text85,0)

work fine when I move the display boxes to the report footer, I guess it's
something to do with the page footer and I really want to keep it on the
footer due to the report lay out. Any suggestion? Thanks
Mark

Hi John,

Thanks for the tips. The code:

DSum("zQuantity","[Main Log Table]","[Type]='Letter' AND Sig_Date is Not
null")

works very good. I have another question thought, let say I have 2 code
statements for 2 display text boxes as below:

Display text box name Text10:
=DSum("zQuantity","[Main Log Table]","[Type]='Letter' AND Sig_Date is Not
null")

and
Display text box name Text11:
=DSum("zQuantity","[Main Log Table]","[Type]='Letter' AND Sig_Date is null")

when I have 15 ( Sum value of display text10) for the quantiy with signature
( [Sig_Date] is not null) and nothing/blank (Sum Value of display Text11) for
the unsigned ([Sig_Date] is null), when I try to add up these 2 field and
display on Text13 by using the statement below:
=Val(Text10)+Val(Text11)
as well as
=Cint(Text10)+Cint(Text11)

but both resulted in #ERROR message. Is there a way to force the display of
Text11 to 0(Zero) or to ignore that blank and treat it as 0 for the
calculation?

Thanks
Mark

" I had to move the item to the page footer"

[quoted text clipped - 21 lines]