Nz problem

[TuffyE]

As the solution to "filling" a crosstab, it was suggested that I use this
function. The line is:

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND

However, that generates some results that I just can't understand when I
analyze it with Excel. The data part of the output table appears to have
values, some of which are "0" and some are not. The first thing that I
noticed was that the Excel SUM function would always come up with a result of
0, whether or not there appear to be any other values or not. Hmmm, sounds
like a Text problem with the 0 filling. So, I tried a SUM with only the
values and that still yielded 0. Just for the heck of it, I tried a simple
cell addition (eg. =G2+G4) and that does add (18 in example below). What
does all of that mean? I didn't think there was that kind of difference
between "=SUM(G2,G4)" and "=G2+G4". Does the Nz cause it to output text
rather than values as the count? Is there a values version that will yield
"0" where that is the COUNT result?

000 1320 2059 0 0 1 9
000 1326 13 0 0 0 0
000 1330 346 0 0 3 9
000 1340 13 0 0 0 0
000 1350 24 0 0 0 0
000 1370 35 0 0 0 0
000 1390 53 0 0 0 0
000 1400 143 0 0 0 0
000 1410 4 0 0 0 0

2690 0 0 0 0
0
18

 

[Allen Browne]

JET 4 (i.e. Access 2000 and later) gets confused when you use Nz() and often
treats the results as text instead of numbers.

The workaround is to explicitly typecast the results. Use CCur() for
currency, CLng() for whole numbers, or CDbl() for fractional numbers.
Example:
TRANSFORM CLng(Nz(Count(RECV.[BAND]),0)) AS CountOfBAND

I'm not an Excel person, but I would have used
=Sum(G2:G4)

More info on typecasting in Access:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put the Nz() function inside the COUNT() aggregate function:

TRANSFORM COUNT(Nz(RECV.BAND,0)) As CountOfBand

Actually, my guess is you want to sum not count. Count() will count
zero as 1 - COUNT(0) = 1. SUM(0) = 0.

TRANSFORM SUM(Nz(RECV.BAND,0)) As BandSum

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUrF6YechKqOuFEgEQLkiwCcCNdb+XdJu6zHojiMjx2KkayKNbYAn1U8
GBbiZgIDVJo8K9CHPU6pqVVw
=q6pb
-----END PGP SIGNATURE-----

 

[TuffyE]

Allen,

That does appear to take care of it. It's odd to me that the problem could
be there. What could be more common than performing an Nz on a crosstab
query or trying to analyze the result in Excel?

You're right, the actual function would be SUM(G2:G10) in my example but I
was showing the version that had only the cells with values in it and how it
differed from just adding those same two cells.

Tuffy

JET 4 (i.e. Access 2000 and later) gets confused when you use Nz() and often
treats the results as text instead of numbers.

The workaround is to explicitly typecast the results. Use CCur() for
currency, CLng() for whole numbers, or CDbl() for fractional numbers.
Example:
TRANSFORM CLng(Nz(Count(RECV.[BAND]),0)) AS CountOfBAND

I'm not an Excel person, but I would have used
=Sum(G2:G4)

More info on typecasting in Access:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

As the solution to "filling" a crosstab, it was suggested that I use this
function. The line is:

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND

However, that generates some results that I just can't understand when I
analyze it with Excel. The data part of the output table appears to have
values, some of which are "0" and some are not. The first thing that I
noticed was that the Excel SUM function would always come up with a result
of
0, whether or not there appear to be any other values or not. Hmmm,
sounds
like a Text problem with the 0 filling. So, I tried a SUM with only the
values and that still yielded 0. Just for the heck of it, I tried a
simple
cell addition (eg. =G2+G4) and that does add (18 in example below). What
does all of that mean? I didn't think there was that kind of difference
between "=SUM(G2,G4)" and "=G2+G4". Does the Nz cause it to output text
rather than values as the count? Is there a values version that will
yield
"0" where that is the COUNT result?

000 1320 2059 0 0 1 9
000 1326 13 0 0 0 0
000 1330 346 0 0 3 9
000 1340 13 0 0 0 0
000 1350 24 0 0 0 0
000 1370 35 0 0 0 0
000 1390 53 0 0 0 0
000 1400 143 0 0 0 0
000 1410 4 0 0 0 0

2690 0 0 0 0
0
18

 

[TuffyE]

As noted, I first tried Allen's solution and it seems to work fine. I will
try this, too, but it makes better sense to me to set the output type after
the calculation. On the other hand, I can understand performing the
calculation on data that has already been "set". Thank you for the idea.

No, there are no numerical fields in the data (part in question, anyway) and
I did intend to COUNT to develop the crosstab query data. Once it is being
analyzed in Excel, it is often necessary to SUM rows and columns of those
COUNTS and those were the steps being taken.

Tuffy

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put the Nz() function inside the COUNT() aggregate function:

TRANSFORM COUNT(Nz(RECV.BAND,0)) As CountOfBand

Actually, my guess is you want to sum not count. Count() will count
zero as 1 - COUNT(0) = 1. SUM(0) = 0.

TRANSFORM SUM(Nz(RECV.BAND,0)) As BandSum

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUrF6YechKqOuFEgEQLkiwCcCNdb+XdJu6zHojiMjx2KkayKNbYAn1U8
GBbiZgIDVJo8K9CHPU6pqVVw
=q6pb
-----END PGP SIGNATURE-----

As the solution to "filling" a crosstab, it was suggested that I use this
function. The line is:

TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND

However, that generates some results that I just can't understand when I
analyze it with Excel. The data part of the output table appears to have
values, some of which are "0" and some are not. The first thing that I
noticed was that the Excel SUM function would always come up with a result of
0, whether or not there appear to be any other values or not. Hmmm, sounds
like a Text problem with the 0 filling. So, I tried a SUM with only the
values and that still yielded 0. Just for the heck of it, I tried a simple
cell addition (eg. =G2+G4) and that does add (18 in example below). What
does all of that mean? I didn't think there was that kind of difference
between "=SUM(G2,G4)" and "=G2+G4". Does the Nz cause it to output text
rather than values as the count? Is there a values version that will yield
"0" where that is the COUNT result?

000 1320 2059 0 0 1 9
000 1326 13 0 0 0 0
000 1330 346 0 0 3 9
000 1340 13 0 0 0 0
000 1350 24 0 0 0 0
000 1370 35 0 0 0 0
000 1390 53 0 0 0 0
000 1400 143 0 0 0 0
000 1410 4 0 0 0 0

2690 0 0 0 0
0
18