What is the difference with these 2 expressions?

[Tony Williams]

I am trying to unravel a report that someone has created (and since left)
and am getting an error in a calculated control.
In one control there is this expression

Round([txtprevqtrtotal8],0)

and in another is this

Round(Nz([txtprevqtrtotal9],0),0)

The first one works ok but the second one shows #Error I'm trying to figure
out why they would have inserted the Nz expression and what does it do. I've
searched help but can only find info on Nz in Visual Basic which doesn't
help me.

Can anyone help?
Thanks
Tony

 

[Klatuu]

If txtprevqtrtotal8 is null,
Round([txtprevqtrtotal8],0)
Will return Null
If txtprevqtrtotal9 is null,
Round(Nz([txtprevqtrtotal9],0),0)
Will return 0

Both expressions are valid.
I do notice a difference in the name. Is the name difference correct?
are the names the name of controls or recordset fields?
Are both preceded by =

 

[Tony Williams]

Hi Thanks Dave
Yes the names are correct. I couldn't understand why one expression had Nz
and the other didn't. I want the expression to
return 0 or a number so I'll change the other to include the Nz as
presumably if I then try and add the expressions without the Nzo will the
Null value cause problems?
Thanks again.
Tony

If txtprevqtrtotal8 is null,
Round([txtprevqtrtotal8],0)
Will return Null
If txtprevqtrtotal9 is null,
Round(Nz([txtprevqtrtotal9],0),0)
Will return 0

Both expressions are valid.
I do notice a difference in the name. Is the name difference correct?
are the names the name of controls or recordset fields?
Are both preceded by =

--
Dave Hargis, Microsoft Access MVP

I am trying to unravel a report that someone has created (and since left)
and am getting an error in a calculated control.
In one control there is this expression

Round([txtprevqtrtotal8],0)

and in another is this

Round(Nz([txtprevqtrtotal9],0),0)

The first one works ok but the second one shows #Error I'm trying to
figure
out why they would have inserted the Nz expression and what does it do.
I've
searched help but can only find info on Nz in Visual Basic which doesn't
help me.

Can anyone help?
Thanks
Tony

 

[Klatuu]

The Nz should not cause a problem. All it does is replace a Null value in
the first argument with the value of the second argument. If the value of
the first argument is not Null, it returns that value.
--
Dave Hargis, Microsoft Access MVP

Hi Thanks Dave
Yes the names are correct. I couldn't understand why one expression had Nz
and the other didn't. I want the expression to
return 0 or a number so I'll change the other to include the Nz as
presumably if I then try and add the expressions without the Nzo will the
Null value cause problems?
Thanks again.
Tony

If txtprevqtrtotal8 is null,
Round([txtprevqtrtotal8],0)
Will return Null
If txtprevqtrtotal9 is null,
Round(Nz([txtprevqtrtotal9],0),0)
Will return 0

Both expressions are valid.
I do notice a difference in the name. Is the name difference correct?
are the names the name of controls or recordset fields?
Are both preceded by =

--
Dave Hargis, Microsoft Access MVP

I am trying to unravel a report that someone has created (and since left)
and am getting an error in a calculated control.
In one control there is this expression

Round([txtprevqtrtotal8],0)

and in another is this

Round(Nz([txtprevqtrtotal9],0),0)

The first one works ok but the second one shows #Error I'm trying to
figure
out why they would have inserted the Nz expression and what does it do.
I've
searched help but can only find info on Nz in Visual Basic which doesn't
help me.

Can anyone help?
Thanks
Tony

 

[Tony Williams]

Thanks Dave
Tony

The Nz should not cause a problem. All it does is replace a Null value in
the first argument with the value of the second argument. If the value of
the first argument is not Null, it returns that value.
--
Dave Hargis, Microsoft Access MVP

Hi Thanks Dave
Yes the names are correct. I couldn't understand why one expression had
Nz
and the other didn't. I want the expression to
return 0 or a number so I'll change the other to include the Nz as
presumably if I then try and add the expressions without the Nzo will the
Null value cause problems?
Thanks again.
Tony

If txtprevqtrtotal8 is null,
Round([txtprevqtrtotal8],0)
Will return Null
If txtprevqtrtotal9 is null,
Round(Nz([txtprevqtrtotal9],0),0)
Will return 0

Both expressions are valid.
I do notice a difference in the name. Is the name difference correct?
are the names the name of controls or recordset fields?
Are both preceded by =

--
Dave Hargis, Microsoft Access MVP


:

I am trying to unravel a report that someone has created (and since
left)
and am getting an error in a calculated control.
In one control there is this expression

Round([txtprevqtrtotal8],0)

and in another is this

Round(Nz([txtprevqtrtotal9],0),0)

The first one works ok but the second one shows #Error I'm trying to
figure
out why they would have inserted the Nz expression and what does it
do.
I've
searched help but can only find info on Nz in Visual Basic which
doesn't
help me.

Can anyone help?
Thanks
Tony

 

[Marshall Barton]

I am trying to unravel a report that someone has created (and since left)
and am getting an error in a calculated control.
In one control there is this expression

Round([txtprevqtrtotal8],0)

and in another is this

Round(Nz([txtprevqtrtotal9],0),0)

The first one works ok but the second one shows #Error I'm trying to figure
out why they would have inserted the Nz expression and what does it do. I've
searched help but can only find info on Nz in Visual Basic which doesn't
help me.

A couple of reasons for getting #Error:

The text box with the expression is named txtprevqtrtotal9.

The value of txtprevqtrtotal9 is a text string that can not
be converted to a number.