Summing Text fields using NZ function causing error

[bschuler]

I am trying to create a sum total for several text fields. I am using the
following query:
SELECT [Netxpro Client Holdings].CUSIP, [Netxpro Client Holdings].[Security
Description], [Bloomberg - All holdings].[Security Type], [Bloomberg - All
holdings].Cpn, [Netxpro Client Holdings].Quantity, [Netxpro Client
Holdings].Value, [Bloomberg - All holdings].Jan, [Bloomberg - All
holdings].Feb, [Bloomberg - All holdings].Mar, [Bloomberg - All
holdings].Apr, [Bloomberg - All holdings].May, [Bloomberg - All
holdings].Jun, [Bloomberg - All holdings].Jul, [Bloomberg - All
holdings].Aug, [Bloomberg - All holdings].Sep, [Bloomberg - All
holdings].Oct, [Bloomberg - All holdings].Nov, [Bloomberg - All
holdings].Dec,
(NZ([Jan],0)+NZ([Feb],0)+NZ([Mar],0)+NZ([Apr],0)+NZ([May],0)+NZ([Jun],0)+NZ([Jul],0)+NZ([Aug],0)+NZ([Sep],0)+NZ([Oct],0)+NZ([Nov],0)+NZ([Dec],0))*[Quantity] AS AnnualInc

FROM [Netxpro Client Holdings] LEFT JOIN [Bloomberg - All holdings] ON
[Netxpro Client Holdings].CUSIP = [Bloomberg - All holdings].Cusip;


I am using the NZ function because some of the field records contain null
values. For these records I obtain the results I would expect. The problem
occurs when there is not a null value for any of fields for a record. In
these instances the function creates an error.

 

[MGFoster]

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

What's the error?

Probably has something to do w/ overflow or data type mismatch. My
guess is that adding all the Jan thru Dec values overflows the data type
of the month columns; or, multiplying by Quantity causes the same error.
To correct these errors you'll have to be sure the values used are the
same data type, or, during the calculation, can be converted to a
suitable data type.

BTW, having columns for each month is bad table design. Do you have a
good reason for putting your month values in separate columns?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQfl0U4echKqOuFEgEQKg0gCgrqKcAGMGldSSwiLS/iul+g5sK9sAoIby
NPtmypi9jyPVAohtoFHmqiA3
=/cS6
-----END PGP SIGNATURE-----

 

[Chris2]

I am trying to create a sum total for several text fields. I am using the
following query:

I am using the NZ function because some of the field records contain null
values. For these records I obtain the results I would expect. The problem
occurs when there is not a null value for any of fields for a record. In
these instances the function creates an error.

SELECT [Netxpro Client Holdings].CUSIP
,[Netxpro Client Holdings].[Security Description]
,[Bloomberg - All holdings].[Security Type]
,[Bloomberg - All holdings].Cpn
,[Netxpro Client Holdings].Quantity
,[Netxpro Client Holdings].Value
,[Bloomberg - All holdings].Jan
,[Bloomberg - All holdings].Feb
,[Bloomberg - All holdings].Mar
,[Bloomberg - All holdings].Apr
,[Bloomberg - All holdings].May
,[Bloomberg - All holdings].Jun
,[Bloomberg - All holdings].Jul
,[Bloomberg - All holdings].Aug
,[Bloomberg - All holdings].Sep
,[Bloomberg - All holdings].Oct
,[Bloomberg - All holdings].Nov
,[Bloomberg - All holdings].Dec
,(NZ([Jan],0)
+ NZ([Feb],0)
+ NZ([Mar],0)
+ NZ([Apr],0)
+ NZ([May],0)
+ NZ([Jun],0)
+ NZ([Jul],0)
+ NZ([Aug],0)
+ NZ([Sep],0)
+ NZ([Oct],0)
+ NZ([Nov],0)
+ NZ([Dec],0)) * [Quantity] AS AnnualInc
FROM [Netxpro Client Holdings]
LEFT JOIN
[Bloomberg - All holdings]
ON [Netxpro Client Holdings].CUSIP = [Bloomberg - All
holdings].Cusip;


This produces an error *only* when all columns in the function are NOT
NULL? That's pretty wild.

I'm curious what error is it producing (or is it the "unknown error"
error)?


However, whenever any type of executable function isn't working,
diagnosis can be aided by copying it and pasting it into the Immediate
Window of the VBA IDE.

I copied the function, unaltered, directly from the original post. I
removed each month and replaced it with a 1, and replaced quantity
with 500.

Then I hit enter.

?
(NZ(1,0)+NZ(1,0)+NZ(1,0)+NZ(1,0)+NZ(1,0)+NZ(1,0)+NZ(1,0)+NZ(1,0)+NZ(1,
0)+NZ(1,0)+NZ(1,0)+NZ(1,0)) * 500

I get 6000. As you can see, all values are NOT NULL, which makes me
think that both the expression itself and the Nz function aren't the
problem.


Now I really want to know what error it was throwing.


Sincerely,

Chris O.