If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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" works maybe I have to use nz(amount,0) even though normally I don't need the comma and zero. Thanks, J. |
#3
|
|||
|
|||
cannot use nz function
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] |
#4
|
|||
|
|||
cannot use nz function
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] |
#5
|
|||
|
|||
cannot use nz function
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] |
#6
|
|||
|
|||
cannot use nz function
"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] |
Thread Tools | |
Display Modes | |
|
|