cannot use nz function

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.

On Sat, 15 Aug 2009 14:52:40 +1200, "Jason" wrote:

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.


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.
--

John W. Vinson [MVP]

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.
"John W. Vinson" wrote in message
...
On Sat, 15 Aug 2009 14:52:40 +1200, "Jason" wrote:

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.


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.
--

John W. Vinson [MVP]

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
'================================================= ===


Jason wrote:
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.
"John W. Vinson" wrote in message
...
On Sat, 15 Aug 2009 14:52:40 +1200, "Jason" wrote:

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.

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.
--

John W. Vinson [MVP]

"John Spencer" wrote in message
...
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
'================================================= ===


Jason wrote:
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.
"John W. Vinson" wrote in message
...
On Sat, 15 Aug 2009 14:52:40 +1200, "Jason" wrote:

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.

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.
--

John W. Vinson [MVP]