NZ(abs(sum()))?

[AnhCVL via AccessMonster.com]

Hi all,

I am creating a report and sort of running into a problem. I have a report
contains files version of "Copy" and "Original" and I am trying to count the
total number of each on the report footer by using Abs(sum()) and it work but
when there is no record, it display #error and I want to avoid that. I've
tried to employ the Nz() function but has failed so far. Below are the code
i've tried:


=Abs(nz(Sum([FVERSION]="COPY"),0))

=Abs(Sum(NZ([FVERSION]="COPY"),0))

Any suggestion?

Thanks

 

[Jeff Boyce]

I'm having trouble understanding how you can "sum" (i.e., add up) a text
value...

If you are comparing [FVersion] to "copy", I would think you would want to
know the COUNT, not the SUM.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[AnhCVL via AccessMonster.com]

Hi Jeff,

Thanks for the quick response. I am trying to count the total of the field
name "FVERSION" where the value is "COPY" and I want the text box to display
"0" instead of "#Error" when there is no data on the report. I am now using
the statements below:

=Abs(Sum([FVERSION]="ORIGINAL"))

=Abs(Sum([FVERSION]="COPY"))

These 2 statements gave the the total count of the numbers of the field name
"FVERSION" when the report contain data but generated "#Error" when the
report is blank so I want the text field which contains the code to display
"0" instead of "#Error" when there is no data on the report. I've tried
couple different way of the Nz() function but has been failing so far.

=Abs(Sum(NZ([FVERSION]="COPY"),0))
=Abs(nz(Sum([FVERSION]="COPY"),0))
=Nz(count([FVERSION]="Copy")),0)

All the above code had gave me "#Error" as result. Any suggestion?

Thanks!

I'm having trouble understanding how you can "sum" (i.e., add up) a text
value...

If you are comparing [FVersion] to "copy", I would think you would want to
know the COUNT, not the SUM.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,

[quoted text clipped - 15 lines]

Thanks

 

[Marshall Barton]

I am creating a report and sort of running into a problem. I have a report
contains files version of "Copy" and "Original" and I am trying to count the
total number of each on the report footer by using Abs(sum()) and it work but
when there is no record, it display #error and I want to avoid that. I've
tried to employ the Nz() function but has failed so far. Below are the code
i've tried:


=Abs(nz(Sum([FVERSION]="COPY"),0))

=Abs(Sum(NZ([FVERSION]="COPY"),0))

Right. The sum of nothing is kind of meaninless, not even
Null.

There are a couple of convoluted ways to make that kind of
expression work, but its pretty much a nonproductive
pursuit.

A way better way is to create a Totals query that calculates
the count for all FVERSIONs and then use the query as the
record source for a subreport.

 

[AnhCVL via AccessMonster.com]

Hi Marshall.

Thanks for the recommendation. I had the report run on a query to filter out
some other info before the report being generated and the total number of
record on the "FVERSION" field is counted base on two different crietia,
which is "Copy" and "Original" and display at 2 text field at the report
footer. I've tried your suggestion and encountered error message when the
report is being generating as "the Criteria is too complex to be calculate or
other info is missing", and if I go into the source query of the report and
do a "sum>gruop by" this yielded inaccurate result at the display fields for
the records count and still showing "#Error" at an empty report. I am not
sure why it reacted that way, probably I am doing something wrong here. Is
there any other hope?

Thanks!
Mark

I am creating a report and sort of running into a problem. I have a report
contains files version of "Copy" and "Original" and I am trying to count the

[quoted text clipped - 6 lines]

=Abs(Sum(NZ([FVERSION]="COPY"),0))

Right. The sum of nothing is kind of meaninless, not even
Null.

There are a couple of convoluted ways to make that kind of
expression work, but its pretty much a nonproductive
pursuit.

A way better way is to create a Totals query that calculates
the count for all FVERSIONs and then use the query as the
record source for a subreport.

 

[Jeff Boyce]

?Perhaps you need to use that Nz() statement on the field [FVersion], so
that nulls in the field show up as zeros?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

Thanks for the quick response. I am trying to count the total of the field
name "FVERSION" where the value is "COPY" and I want the text box to
display
"0" instead of "#Error" when there is no data on the report. I am now
using
the statements below:

=Abs(Sum([FVERSION]="ORIGINAL"))

=Abs(Sum([FVERSION]="COPY"))

These 2 statements gave the the total count of the numbers of the field
name
"FVERSION" when the report contain data but generated "#Error" when the
report is blank so I want the text field which contains the code to
display
"0" instead of "#Error" when there is no data on the report. I've tried
couple different way of the Nz() function but has been failing so far.

=Abs(Sum(NZ([FVERSION]="COPY"),0))
=Abs(nz(Sum([FVERSION]="COPY"),0))
=Nz(count([FVERSION]="Copy")),0)

All the above code had gave me "#Error" as result. Any suggestion?

Thanks!

I'm having trouble understanding how you can "sum" (i.e., add up) a text
value...

If you are comparing [FVersion] to "copy", I would think you would want to
know the COUNT, not the SUM.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,

[quoted text clipped - 15 lines]

Thanks

 

[AnhCVL via AccessMonster.com]

Jeff,

that's exactly what I am trying to do but something with the syntax that
preventing me from getting the desored result. The Fversion field is a Text
field, it can either contains "Copy" or "Original" and when there is no
record on thr report, there will be nothing show, and that's when the
"#Error" problem come to take place because of the statement
Abs(sum()) I am suing to count the total record on the footer base on the
criteria of "copy" or "original". and so far, I've tried different methods
and all the suggestion from you guys but has failed. Any other suggestions
and attempts are greatly appreaciated.

Mark

?Perhaps you need to use that Nz() statement on the field [FVersion], so
that nulls in the field show up as zeros?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

[quoted text clipped - 41 lines]

 

[Jeff Boyce]

Is the underlying field in your table defined as "allow zero-length string"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

that's exactly what I am trying to do but something with the syntax that
preventing me from getting the desored result. The Fversion field is a
Text
field, it can either contains "Copy" or "Original" and when there is no
record on thr report, there will be nothing show, and that's when the
"#Error" problem come to take place because of the statement
Abs(sum()) I am suing to count the total record on the footer base on the
criteria of "copy" or "original". and so far, I've tried different methods
and all the suggestion from you guys but has failed. Any other suggestions
and attempts are greatly appreaciated.

Mark

?Perhaps you need to use that Nz() statement on the field [FVersion], so
that nulls in the field show up as zeros?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

[quoted text clipped - 41 lines]

Thanks

 

[Douglas J. Steele]

Try:

=Abs(Sum(Nz([FVERSION], "")="COPY"))

What that does is use the Nz function to convert null values in [FVersion]
to zero-length strings so that you can use = to compare them. The comparison
will result in either 0 or -1, which you then sum and take the absolute.

 

[Marshall Barton]

If the main report's record source is query1, then I would
think the summary subreport's record source could be
something like:

SELECT FVERSION, Count(*) As VerCount
FROM query1
GROUP BY FVERSION
--
Marsh
MVP [MS Access]

Thanks for the recommendation. I had the report run on a query to filter out
some other info before the report being generated and the total number of
record on the "FVERSION" field is counted base on two different crietia,
which is "Copy" and "Original" and display at 2 text field at the report
footer. I've tried your suggestion and encountered error message when the
report is being generating as "the Criteria is too complex to be calculate or
other info is missing", and if I go into the source query of the report and
do a "sum>gruop by" this yielded inaccurate result at the display fields for
the records count and still showing "#Error" at an empty report. I am not
sure why it reacted that way, probably I am doing something wrong here. Is
there any other hope?

I am creating a report and sort of running into a problem. I have a report
contains files version of "Copy" and "Original" and I am trying to count the

[quoted text clipped - 6 lines]

=Abs(Sum(NZ([FVERSION]="COPY"),0))

Right. The sum of nothing is kind of meaninless, not even
Null.

There are a couple of convoluted ways to make that kind of
expression work, but its pretty much a nonproductive
pursuit.

A way better way is to create a Totals query that calculates
the count for all FVERSIONs and then use the query as the
record source for a subreport.

 

[AnhCVL via AccessMonster.com]

Hi Douglas,

Thank you for the suggestion. I tried the suggestion but it generated
"#Error" still. I've also tried to place Nz() before Abs() as well as before
Sum() but does help. Do you think if I use it with Count() wuold make any
different? I am gonna try but please let me know.

Mark

Try:

=Abs(Sum(Nz([FVERSION], "")="COPY"))

What that does is use the Nz function to convert null values in [FVersion]
to zero-length strings so that you can use = to compare them. The comparison
will result in either 0 or -1, which you then sum and take the absolute.

Hi all,

[quoted text clipped - 15 lines]

Thanks

 

[AnhCVL via AccessMonster.com]

Hi Marshall,

This method gave me a total count of both copy and original but I want it to
be displayed in two separated field each type, and I am not familiar with sql
statement, any suggestion on how to get them sep?

Thanks

If the main report's record source is query1, then I would
think the summary subreport's record source could be
something like:

SELECT FVERSION, Count(*) As VerCount
FROM query1
GROUP BY FVERSION

Thanks for the recommendation. I had the report run on a query to filter out
some other info before the report being generated and the total number of

[quoted text clipped - 24 lines]

 

[Marshall Barton]

That's what the subreport does.

Create a little report based on the query. When you get it
to display the way you want, then just drag it from the db
window and drop it into the report footer section.
--
Marsh
MVP [MS Access]

This method gave me a total count of both copy and original but I want it to
be displayed in two separated field each type, and I am not familiar with sql
statement, any suggestion on how to get them sep?

Thanks

If the main report's record source is query1, then I would
think the summary subreport's record source could be
something like:

SELECT FVERSION, Count(*) As VerCount
FROM query1
GROUP BY FVERSION

Thanks for the recommendation. I had the report run on a query to filter out
some other info before the report being generated and the total number of

[quoted text clipped - 24 lines]

the count for all FVERSIONs and then use the query as the
record source for a subreport.

 

[Douglas J. Steele]

Try:

=Sum(IIf(Nz([FVERSION], "")="COPY", 1, 0))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas,

Thank you for the suggestion. I tried the suggestion but it generated
"#Error" still. I've also tried to place Nz() before Abs() as well as
before
Sum() but does help. Do you think if I use it with Count() wuold make any
different? I am gonna try but please let me know.

Mark

Try:

=Abs(Sum(Nz([FVERSION], "")="COPY"))

What that does is use the Nz function to convert null values in [FVersion]
to zero-length strings so that you can use = to compare them. The
comparison
will result in either 0 or -1, which you then sum and take the absolute.

Hi all,

[quoted text clipped - 15 lines]

Thanks

 

[GeoffG]

Hi,

You can use the Domain Aggregate function DCount().
Here's how:

1. Create a TextBox in the Report Footer.
2. Open the Properties dialog for the TextBox.
3. Click the Data tab at the top of the dialog.
4. Click in the ControlSource property.
5. Enter the expression:

=DCount("FVERSION","Table1","FVERSION='COPY'")

Please Note:

(a) I am assuming FVERSION is the name of the field that
contains COPY, ORIGINAL, or is blank.

(b) You should replace "Table1" in the above expression with
the name of the table or query that is the RecordSource for the
report.

(c) Be careful after COPY in the above expression. You need a
single quotation mark ( ' ) followed by a double quotation mark
( " ).

6. If you need a count of the records that contain 'ORIGINAL',
then create a separate TextBox and replace 'COPY' with
'ORIGINAL'.

If you need help with domain aggregate functions, after step 4
above, click the build button (...) on the right of the
ControlSource property. This will open the Expression Builder
dialog. In the dialog, double-click 'Functions' in the left
list; then double-click 'Built-In Functions'. In the middle
list, click 'Domain Aggregate'. In the right list, click
'DCount'. Then click the Help button. This will take you to the
help screen for the DCount method. That will give you an idea of
what to do.

Just to end with, personally, I don't much like domain aggregate
functions - like DCount() - because they can be slow. But you
may find it works fine for your application.

Regards
Geoff

 

[AnhCVL via AccessMonster.com]

Hi Geo,

Thanks for the suggestion. I will try it and return with the result.Thanks
again.

Hi,

You can use the Domain Aggregate function DCount().
Here's how:

1. Create a TextBox in the Report Footer.
2. Open the Properties dialog for the TextBox.
3. Click the Data tab at the top of the dialog.
4. Click in the ControlSource property.
5. Enter the expression:

=DCount("FVERSION","Table1","FVERSION='COPY'")

Please Note:

(a) I am assuming FVERSION is the name of the field that
contains COPY, ORIGINAL, or is blank.

(b) You should replace "Table1" in the above expression with
the name of the table or query that is the RecordSource for the
report.

(c) Be careful after COPY in the above expression. You need a
single quotation mark ( ' ) followed by a double quotation mark
( " ).

6. If you need a count of the records that contain 'ORIGINAL',
then create a separate TextBox and replace 'COPY' with
'ORIGINAL'.

If you need help with domain aggregate functions, after step 4
above, click the build button (...) on the right of the
ControlSource property. This will open the Expression Builder
dialog. In the dialog, double-click 'Functions' in the left
list; then double-click 'Built-In Functions'. In the middle
list, click 'Domain Aggregate'. In the right list, click
'DCount'. Then click the Help button. This will take you to the
help screen for the DCount method. That will give you an idea of
what to do.

Just to end with, personally, I don't much like domain aggregate
functions - like DCount() - because they can be slow. But you
may find it works fine for your application.

Regards
Geoff

Hi all,

[quoted text clipped - 17 lines]

Thanks