Nz

[Matt_James via AccessMonster.com]

Hi,
I have a Crosstab query in which I need to convert the empty (null?) values
to 0. I have tried using the Nz function, but it doesn’t seem to work. I know
this is probably simple, but could somebody point me in the right direction?
The field that I need to convert to 0 is wrk_item_1502.

Thanks

 

[Ken Snell \(MVP\)]

Post the SQL statement of the query -- let's see how you're trying to use Nz
function.

 

[John Spencer]

The first line of the crosstab should look something like the following.
Since you didn't post any of your SQL this is the best that I can guess

TRANSFORM CDbl(Nz(Sum([wrk_item_1502]),0)) as Amount
SELECT ...
FROM ...

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

 

[Matt_James via AccessMonster.com]

Here it is.
(Nz(Sum(1502),0))

Post the SQL statement of the query -- let's see how you're trying to use Nz
function.

Hi,
I have a Crosstab query in which I need to convert the empty (null?)

[quoted text clipped - 6 lines]

Thanks

 

[Matt_James via AccessMonster.com]

Sorry I was wrong earlier. The Field heading is
Totalof101_102_111_1502_Estworkdays_07: Est_workdays_07 and I need to replace
the null values in 1502 with 0. Is this possible.

The first line of the crosstab should look something like the following.
Since you didn't post any of your SQL this is the best that I can guess

TRANSFORM CDbl(Nz(Sum([wrk_item_1502]),0)) as Amount
SELECT ...
FROM ...

Hi,
I have a Crosstab query in which I need to convert the empty (null?)

[quoted text clipped - 6 lines]

Thanks

 

[John Spencer]

POST the ENTIRE SQL statement please.

What you have posted so far is fragments of the SQL statement.

Hint: Open the query, switch to SQL view (View: SQL) and copy and paste the
statement that is there.

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

Sorry I was wrong earlier. The Field heading is
Totalof101_102_111_1502_Estworkdays_07: Est_workdays_07 and I need to
replace
the null values in 1502 with 0. Is this possible.

The first line of the crosstab should look something like the following.
Since you didn't post any of your SQL this is the best that I can guess

TRANSFORM CDbl(Nz(Sum([wrk_item_1502]),0)) as Amount
SELECT ...
FROM ...

Hi,
I have a Crosstab query in which I need to convert the empty (null?)

[quoted text clipped - 6 lines]

Thanks

 

[Matt_James via AccessMonster.com]

Here is the entire SQL. I need to convert the null values in the 1502 field
so I can calculate 86% of that and add it to the others and subtract it from
the Total_202_1416_1701to get the total CO_WL. I hope this makes sense since
i'm a novice user being asked to design a complex database. Thanks for help.

SELECT [06CO_Minus_GSwrk_Crosstab].FIPS_NUM, [06CO_Minus_GSwrk_Crosstab].CT,
[06CO_Minus_GSwrk_Crosstab].CTY_NAME, [06CO_Minus_GSwrk_Crosstab].[Total Of
Actual_workdays_06], FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.
[101], [101]*0.764 AS 764percentof101,
FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.[102], [102]*0.745
AS 745percentof102, FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.
[111], [111]*0.959 AS 959percentof111, (Nz(Sum(1502),0)) AS 1502, [1502]*0.86
AS 86percentof1502, [06Total_202's_1416's_1701's_Query].Total_202_1416_1701,
Round(([Total Of Actual_workdays_06]+[764percentof101]+[745percentof102]+
[959percentof111]+[86percentof1502])-([2110]))-([Total_202_1416_1701]) AS
CO_WL
FROM [06Total_202's_1416's_1701's_Query] INNER JOIN
(FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab INNER JOIN
06CO_Minus_GSwrk_Crosstab ON
FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.FIPS_NUM =
[06CO_Minus_GSwrk_Crosstab].FIPS_NUM) ON ([06Total_202's_1416's_1701's_Query].
FIPS_NUM = FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.FIPS_NUM)
AND ([06Total_202's_1416's_1701's_Query].FIPS_NUM =
[06CO_Minus_GSwrk_Crosstab].FIPS_NUM);

POST the ENTIRE SQL statement please.

What you have posted so far is fragments of the SQL statement.

Hint: Open the query, switch to SQL view (View: SQL) and copy and paste the
statement that is there.

Sorry I was wrong earlier. The Field heading is
Totalof101_102_111_1502_Estworkdays_07: Est_workdays_07 and I need to

[quoted text clipped - 13 lines]

 

[Matt_James via AccessMonster.com]

Here is the entire SQL. I need to convert the null values in the 1502 field
so I can calculate 86% of that and add it to the others and subtract it from
the Total_202_1416_1701to get the total CO_WL. I hope this makes sense since
i'm a novice user being asked to design a complex database. Thanks for help.

SELECT [06CO_Minus_GSwrk_Crosstab].FIPS_NUM, [06CO_Minus_GSwrk_Crosstab].CT,
[06CO_Minus_GSwrk_Crosstab].CTY_NAME, [06CO_Minus_GSwrk_Crosstab].[Total Of
Actual_workdays_06], FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.
[101], [101]*0.764 AS 764percentof101,
FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.[102], [102]*0.745
AS 745percentof102, FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.
[111], [111]*0.959 AS 959percentof111, (Nz(Sum(1502),0)) AS 1502, [1502]*0.86
AS 86percentof1502, [06Total_202's_1416's_1701's_Query].Total_202_1416_1701,
Round(([Total Of Actual_workdays_06]+[764percentof101]+[745percentof102]+
[959percentof111]+[86percentof1502])-([2110]))-([Total_202_1416_1701]) AS
CO_WL
FROM [06Total_202's_1416's_1701's_Query] INNER JOIN
(FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab INNER JOIN
06CO_Minus_GSwrk_Crosstab ON
FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.FIPS_NUM =
[06CO_Minus_GSwrk_Crosstab].FIPS_NUM) ON ([06Total_202's_1416's_1701's_Query].
FIPS_NUM = FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.FIPS_NUM)
AND ([06Total_202's_1416's_1701's_Query].FIPS_NUM =
[06CO_Minus_GSwrk_Crosstab].FIPS_NUM);

Or heres the calculated field CO_WL: Round(([Total Of Actual_workdays_06]+[764percentof101]+[745percentof102]+[959percentof111]+[86percentof1502])-([2110]))-([Total_202_1416_1701])

In which i need the null values in 1502 to be 0 so that I don't get a blank
record for CO_WL

[quoted text clipped - 8 lines]

 

[MGFoster]

Here is the entire SQL. I need to convert the null values in the 1502 field
so I can calculate 86% of that and add it to the others and subtract it from
the Total_202_1416_1701to get the total CO_WL. I hope this makes sense since
i'm a novice user being asked to design a complex database. Thanks for help.

SELECT [06CO_Minus_GSwrk_Crosstab].FIPS_NUM, [06CO_Minus_GSwrk_Crosstab].CT,
[06CO_Minus_GSwrk_Crosstab].CTY_NAME, [06CO_Minus_GSwrk_Crosstab].[Total Of
Actual_workdays_06], FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.
[101], [101]*0.764 AS 764percentof101,
FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.[102], [102]*0.745
AS 745percentof102, FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.
[111], [111]*0.959 AS 959percentof111, (Nz(Sum(1502),0)) AS 1502, [1502]*0.86
AS 86percentof1502, [06Total_202's_1416's_1701's_Query].Total_202_1416_1701,
Round(([Total Of Actual_workdays_06]+[764percentof101]+[745percentof102]+
[959percentof111]+[86percentof1502])-([2110]))-([Total_202_1416_1701]) AS
CO_WL
FROM [06Total_202's_1416's_1701's_Query] INNER JOIN
(FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab INNER JOIN
06CO_Minus_GSwrk_Crosstab ON
FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.FIPS_NUM =
[06CO_Minus_GSwrk_Crosstab].FIPS_NUM) ON ([06Total_202's_1416's_1701's_Query].
FIPS_NUM = FLP_Admin06_wrkitm_101_102_111_1502_CountyQuery_Crosstab.FIPS_NUM)
AND ([06Total_202's_1416's_1701's_Query].FIPS_NUM =
[06CO_Minus_GSwrk_Crosstab].FIPS_NUM);

Or heres the calculated field CO_WL: Round(([Total Of Actual_workdays_06]+[764percentof101]+[745percentof102]+[959percentof111]+[86percentof1502])-([2110]))-([Total_202_1416_1701])

In which i need the null values in 1502 to be 0 so that I don't get a blank
record for CO_WL

[quoted text clipped - 8 lines]

Thanks

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

You have to put square brackets around the 1502, otherwise, the SUM()
function will read it as a constant value instead of the value in the
column named [1502].

For clarity's sake you might want to use a different alias in this
expression:

(Nz(Sum([1502]),0)) AS 1502

Change it to something like this:

(Nz(Sum([1502]),0)) AS 1502A

'Cuz right after that expression you use 1502 as a column name - it
isn't clear if that is the column name or the alias for the expression.
--
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/AwUBRnMcU4echKqOuFEgEQIEPQCgqrTfNwvS00rsSbEQNx0Wp9p2/EAAn24V
ZH63zberMu87Zo7xpWm9K/fk
=g4Bg
-----END PGP SIGNATURE-----

 

[Chris2]

Here it is.
(Nz(Sum(1502),0))

Matt_James,

(Nz(Sum(1502),0))

Nz is operating on the results of the SUM, all NULLs have already been
processed before Nz is fed a value.

Try:

(SUM(Nz(1502,0))

SUM is operating on the results of Nz, and Nz is operating on each
row's data.


That's untested, but hopefully should work.


Sincerely,

Chris O.

 

[Matt_James via AccessMonster.com]

Thanks

Here is the entire SQL. I need to convert the null values in the 1502 field
so I can calculate 86% of that and add it to the others and subtract it from [quoted text clipped - 28 lines]
[quoted text clipped - 8 lines]
Thanks

You have to put square brackets around the 1502, otherwise, the SUM()
function will read it as a constant value instead of the value in the
column named [1502].

For clarity's sake you might want to use a different alias in this
expression:

(Nz(Sum([1502]),0)) AS 1502

Change it to something like this:

(Nz(Sum([1502]),0)) AS 1502A

'Cuz right after that expression you use 1502 as a column name - it
isn't clear if that is the column name or the alias for the expression.