cannot use nz function

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

I have a query (running the query under queries) that works fine with where
nz(amount)<>0 however when I use currentdb.execute "Update amount=0 where
nz(amount)=0" generates an error.
currentdb.execute "Update amount=0 where iif(isnull(amount),0,amount)=0"

Thanks,
J.
 
I have a query (running the query under queries) that works fine with where
nz(amount)<>0 however when I use
currentdb.execute "Update amount=0 where nz(amount)=0" generates an error.
currentdb.execute "Update amount=0 where iif(isnull(amount),0,amount)=0"
works

maybe I have to use nz(amount,0) even though normally I don't need the comma
and zero.

Thanks,
J.
 
I have a query (running the query under queries) that works fine with where
nz(amount)<>0 however when I use currentdb.execute "Update amount=0 where
nz(amount)=0" generates an error.
currentdb.execute "Update amount=0 where iif(isnull(amount),0,amount)=0"

Thanks,
J.
Click to expand...

This is incorrect syntax for an UPDATE statement: it should be

UPDATE tablename
SET fieldname = <some value>
WHERE <condition>

It SOUNDS like what you want to do is to update a field named Amount to 0 if
it is NULL (updating it to 0 when it is already 0 is pointless). The syntax
would be

CurrentDb.Execute "UPDATE yourtable SET Amount=0 WHERE Amount IS NULL"

If that's not what you're trying to accomplish please explain.
 
The numbers are displaying as zero even though they could be 0.00003 so I am
using the nz function to over come the issue should any values be null.
 
UPDATE SomeTable
SET Amount = 0
WHERE Amount is Null or (Amount >0 and Amount <1)

If you want to only change amounts to zero with a smaller range then
change the range to
(Amount >0 and AMount < .0001)
or whatever range you wish to specify.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

The numbers are displaying as zero even though they could be 0.00003 so I am
using the nz function to over come the issue should any values be null.
Click to expand...
 
John Spencer said:
UPDATE SomeTable
SET Amount = 0
WHERE Amount is Null or (Amount >-0.001 and Amount <0.001)

If you want to only change amounts to zero with a smaller range then
change the range to
(Amount >0 and AMount < .0001)
or whatever range you wish to specify.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Click to expand...
 
Back
Top