Calculations with NULL

[Andreas]

How can I calculate values of fields if one has value NULL? I use the
control source property of a field and if a field has null (i.e 4 + null)
the value is null. I can't use the ISNULL function in the control source to
change it to 0. Any other way?

 

[Allen Browne]

Andreas, are you trying to:
b) create another calculated control that treats the value as zero instead
of null, or
a) actually change the value in the field, so it is zero, not null.

If (a), use the Nz() function around the field. For example instead of
using:
= 10 * [Field1]
use:
= 10 * Nz([Field1],0)

If (b), create a query using this table.
Change it to an Update query. (Update on Query menu.)
In the Criteria row under this field, enter:
Is Null
In the Update row, enter:
0
Run the query.

 

[Rick Brandt]

How can I calculate values of fields if one has value NULL? I use the
control source property of a field and if a field has null (i.e 4 +
null) the value is null. I can't use the ISNULL function in the
control source to change it to 0. Any other way?

Nz([FieldName,0) + 4

 

[Van T. Dinh]

Check Access Help on the Nz() function ...

 

[Andreas]

Are you sure that this can be used in the control source ?
I use it as follows:
=[Text65]-Nz([AmountPaid],0)

I get an invalid syntax error message, the same I was getting with the
ISNULL function

How can I calculate values of fields if one has value NULL? I use the
control source property of a field and if a field has null (i.e 4 +
null) the value is null. I can't use the ISNULL function in the
control source to change it to 0. Any other way?

Nz([FieldName,0) + 4

 

[Rick Brandt]

Are you sure that this can be used in the control source ?
I use it as follows:
=[Text65]-Nz([AmountPaid],0)

I get an invalid syntax error message, the same I was getting with the
ISNULL function

Do both Text65 and AmountPaid contain numeric values? Did you make sure that
the name of the TextBox is not "Text65" or "AmountPaid"?

There is nothing wrong with that expression as a ControlSource.

 

[Allen Browne]

Andreas, are you trying to:
b) create another calculated control that treats the value as zero instead
of null, or
a) actually change the value in the field, so it is zero, not null.

If (a), use the Nz() function around the field. For example instead of
using:
= 10 * [Field1]
use:
= 10 * Nz([Field1],0)

If (b), create a query using this table.
Change it to an Update query. (Update on Query menu.)
In the Criteria row under this field, enter:
Is Null
In the Update row, enter:
0
Run the query.

 

[Andreas]

Now it worked. You don't need the ,0. It only needs to be:
=[Text65]-Nz([AmountPaid])

Thanks

 

[Allen Browne]

I think you will find that unreliable.

If it doesn't work with the zero, you have a problem with data types here.
Sooner or later, the confusion about whether this is text or number will
cause something to fail or give you incorrect results.

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

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

Now it worked. You don't need the ,0. It only needs to be:
=[Text65]-Nz([AmountPaid])

Thanks

How can I calculate values of fields if one has value NULL? I use the
control source property of a field and if a field has null (i.e 4 + null)
the value is null. I can't use the ISNULL function in the control source
to change it to 0. Any other way?