Null In Crosstab Report

[Guest]

I have a Report based upon crosstab query that I need to perform a %
calculation. If one of the columns is blank (Null) my calculation returns
nothing. I assume I need to somehow convery Null values to 0. How? Where?
eg. below.

Total Inactive Prospect Sold Conv %
Bill 10 2 2 6 80%
Jane 10 2 5
(Should be 5/10=50%)
Thanks,

 

[Duane Hookom]

You can set the control source in the report to something like:
=Nz(Prospect,0)/[Total]

 

[Guest]

I assume (based upon my example) the control source you are referring to is
the Conv%?

Would the code in Conv% be =Sum(Nz(Prospects,0)+(Nz(Sold,0)/Total

You can set the control source in the report to something like:
=Nz(Prospect,0)/[Total]

--
Duane Hookom
MS Access MVP
--

I have a Report based upon crosstab query that I need to perform a %
calculation. If one of the columns is blank (Null) my calculation returns
nothing. I assume I need to somehow convery Null values to 0. How?
Where?
eg. below.

Total Inactive Prospect Sold Conv %
Bill 10 2 2 6 80%
Jane 10 2 5
(Should be 5/10=50%)
Thanks,

 

[Guest]

That worked Thanks.

You can set the control source in the report to something like:
=Nz(Prospect,0)/[Total]

--
Duane Hookom
MS Access MVP
--

I have a Report based upon crosstab query that I need to perform a %
calculation. If one of the columns is blank (Null) my calculation returns
nothing. I assume I need to somehow convery Null values to 0. How?
Where?
eg. below.

Total Inactive Prospect Sold Conv %
Bill 10 2 2 6 80%
Jane 10 2 5
(Should be 5/10=50%)
Thanks,

 

[Guest]

Now the question is How would I display a 0 is the field is a null?

You can set the control source in the report to something like:
=Nz(Prospect,0)/[Total]

--
Duane Hookom
MS Access MVP
--

I have a Report based upon crosstab query that I need to perform a %
calculation. If one of the columns is blank (Null) my calculation returns
nothing. I assume I need to somehow convery Null values to 0. How?
Where?
eg. below.

Total Inactive Prospect Sold Conv %
Bill 10 2 2 6 80%
Jane 10 2 5
(Should be 5/10=50%)
Thanks,

 

[Duane Hookom]

Display a 0 where? In the query or text box? If you want to return a 0 in
the column in the crosstab, you can force this using something like:
theValue: Val(Nz(Sum([YourField),0))

--
Duane Hookom
MS Access MVP
--

Now the question is How would I display a 0 is the field is a null?

You can set the control source in the report to something like:
=Nz(Prospect,0)/[Total]

--
Duane Hookom
MS Access MVP
--

I have a Report based upon crosstab query that I need to perform a %
calculation. If one of the columns is blank (Null) my calculation
returns
nothing. I assume I need to somehow convery Null values to 0. How?
Where?
eg. below.

Total Inactive Prospect Sold Conv %
Bill 10 2 2 6
80%
Jane 10 2 5
(Should be 5/10=50%)
Thanks,

 

[Guest]

Sorrym Duane,

In the Detail Section of my report, if the value does not exist it return a
blank. I would like a zero.

Display a 0 where? In the query or text box? If you want to return a 0 in
the column in the crosstab, you can force this using something like:
theValue: Val(Nz(Sum([YourField),0))

--
Duane Hookom
MS Access MVP
--

Now the question is How would I display a 0 is the field is a null?

You can set the control source in the report to something like:
=Nz(Prospect,0)/[Total]

--
Duane Hookom
MS Access MVP
--

I have a Report based upon crosstab query that I need to perform a %
calculation. If one of the columns is blank (Null) my calculation
returns
nothing. I assume I need to somehow convery Null values to 0. How?
Where?
eg. below.

Total Inactive Prospect Sold Conv %
Bill 10 2 2 6
80%
Jane 10 2 5
(Should be 5/10=50%)
Thanks,

 

[Duane Hookom]

I would rather place the zero in the query unless you don't want it there.
However, if you want to display a zero in a text box in place of a null, you
can use the Format property of the text box to do this. Check Help and
search on Format Property Numeric.

--
Duane Hookom
MS Access MVP
--

Sorrym Duane,

In the Detail Section of my report, if the value does not exist it return
a
blank. I would like a zero.

Display a 0 where? In the query or text box? If you want to return a 0 in
the column in the crosstab, you can force this using something like:
theValue: Val(Nz(Sum([YourField),0))

--
Duane Hookom
MS Access MVP
--

Now the question is How would I display a 0 is the field is a null?

:

You can set the control source in the report to something like:
=Nz(Prospect,0)/[Total]

--
Duane Hookom
MS Access MVP
--

I have a Report based upon crosstab query that I need to perform a %
calculation. If one of the columns is blank (Null) my calculation
returns
nothing. I assume I need to somehow convery Null values to 0. How?
Where?
eg. below.

Total Inactive Prospect Sold Conv %
Bill 10 2 2 6
80%
Jane 10 2 5
(Should be 5/10=50%)
Thanks,