iff null then assume 0 ???

[Guest]

is there a way to make a null value in a control source function as if there
were a 0 (zero) in the record?
Basically, i want to say ;
if the control source = "null" then control source ="0"
thanks

 

[BarryC]

Yes
Nz([Field]) will return zero if the Field is null.

 

[Guest]

thanks BarryC. However, that gives me a lot of #Errors now

 

[Duane Hookom]

Actually it is more proper to include the second arguement
=Nz([Field], 0)
You must make sure that the name of the control is not the name of a field.

It might be easier to set the format property to "display" a 0 for all null
values.

--
Duane Hookom
MS Access MVP
--

thanks BarryC. However, that gives me a lot of #Errors now

Yes
Nz([Field]) will return zero if the Field is null.

 

[Guest]

i need to actually use a zero for other calculations that the report does.
null values are just leaving the fields blank. but if i could make null = 0,
then the rest of the numbers would populate. I think ;S

Actually it is more proper to include the second arguement
=Nz([Field], 0)
You must make sure that the name of the control is not the name of a field.

It might be easier to set the format property to "display" a 0 for all null
values.

--
Duane Hookom
MS Access MVP
--

thanks BarryC. However, that gives me a lot of #Errors now

Yes
Nz([Field]) will return zero if the Field is null.

 

[Duane Hookom]

Create a column/field in your report's record source, like:
Field: NewFieldName: Val(Nz([OldFieldName],0))
--
Duane Hookom
MS Access MVP
--

i need to actually use a zero for other calculations that the report does.
null values are just leaving the fields blank. but if i could make null =
0,
then the rest of the numbers would populate. I think ;S

Actually it is more proper to include the second arguement
=Nz([Field], 0)
You must make sure that the name of the control is not the name of a
field.

It might be easier to set the format property to "display" a 0 for all
null
values.

--
Duane Hookom
MS Access MVP
--

thanks BarryC. However, that gives me a lot of #Errors now


:

Yes
Nz([Field]) will return zero if the Field is null.

 

[Guest]

how do i format the properties to display 0?

Actually it is more proper to include the second arguement
=Nz([Field], 0)
You must make sure that the name of the control is not the name of a field.

It might be easier to set the format property to "display" a 0 for all null
values.

--
Duane Hookom
MS Access MVP
--

thanks BarryC. However, that gives me a lot of #Errors now

Yes
Nz([Field]) will return zero if the Field is null.

 

[Duane Hookom]

Open Help and search on:
Format Property Numeric

--
Duane Hookom
MS Access MVP

how do i format the properties to display 0?

Actually it is more proper to include the second arguement
=Nz([Field], 0)
You must make sure that the name of the control is not the name of a
field.

It might be easier to set the format property to "display" a 0 for all
null
values.

--
Duane Hookom
MS Access MVP
--

thanks BarryC. However, that gives me a lot of #Errors now


:

Yes
Nz([Field]) will return zero if the Field is null.

 

[Guest]

that will not work because the underlying data has null values from an inner
join property from two tables. in other words, it is null because the it does
not exist on the table where the data is displayed from

Open Help and search on:
Format Property Numeric

--
Duane Hookom
MS Access MVP

how do i format the properties to display 0?

Actually it is more proper to include the second arguement
=Nz([Field], 0)
You must make sure that the name of the control is not the name of a
field.

It might be easier to set the format property to "display" a 0 for all
null
values.

--
Duane Hookom
MS Access MVP
--

thanks BarryC. However, that gives me a lot of #Errors now


:

Yes
Nz([Field]) will return zero if the Field is null.

 

[Duane Hookom]

My thought was to apply the format to the text box on the report.

--
Duane Hookom
MS Access MVP

that will not work because the underlying data has null values from an
inner
join property from two tables. in other words, it is null because the it
does
not exist on the table where the data is displayed from

Open Help and search on:
Format Property Numeric

--
Duane Hookom
MS Access MVP

how do i format the properties to display 0?

:

Actually it is more proper to include the second arguement
=Nz([Field], 0)
You must make sure that the name of the control is not the name of a
field.

It might be easier to set the format property to "display" a 0 for all
null
values.

--
Duane Hookom
MS Access MVP
--

thanks BarryC. However, that gives me a lot of #Errors now


:

Yes
Nz([Field]) will return zero if the Field is null.

 

[BarryC]

I think you are missing something here.
Using the Nz() function in the field that you are having problems with
will solve those problems. Nz() returns the number Zero when the
underlying data is NULL. Your columns will sum up and average and
everything else just fine when you use the Nz() function. You don't
need any other fields or columns.

The only pitfall to watch out for is if your report field is named the
same as your datasource fieldname. Rename your report field to
something like "txt[fieldname]" where [fieldname] is the name of the
data field. Watch out for Access to replace the data fieldname in your
formula with the new report field name.