Find Replace values when they are not null
in [Queries]
|
From: teddyb777 on 6 Apr 2010 16:20 I have a table that contains dollar amounts in random cells throughout the table with the remainder of the cells containing nulls. I need to replace every dollar amount with a zero while not interfering with the nulls. Can someone help me with to create an update query that will accomplish this? Thanks so much.
From: PieterLinden via AccessMonster.com on 6 Apr 2010 16:30 teddyb777 wrote: >I have a table that contains dollar amounts in random cells throughout the >table with the remainder of the cells containing nulls. I need to replace >every dollar amount with a zero while not interfering with the nulls. Can >someone help me with to create an update query that will accomplish this? >Thanks so much. oookay. You do realize that Null dollar amount means something different than Zero dollar amount. Null means unknown. Zero does not. But if you're sure you want to do that, use NZ() UPDATE SpreadsheetTable SET SpreadsheetTable.A = Nz([A],0) WHERE (((SpreadsheetTable.A) Is Null)); -- Message posted via http://www.accessmonster.com
From: John Spencer on 6 Apr 2010 16:35 How many FIELDS are involved? UPDATE SomeTable SET FieldA = [FieldA] * 0 , FieldB = [FieldB] * 0 Null * 0 is Null AnyNumberValue * 0 is Zero. If you have only a few records that have values you might be better off doing one a field at a time (multiple queries - one for each field involved) and filtering out the records where the field is Null. UPDATE SomeTable SET FieldA = Null WHERE FieldA is Not Null John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County teddyb777 wrote: > I have a table that contains dollar amounts in random cells throughout the > table with the remainder of the cells containing nulls. I need to replace > every dollar amount with a zero while not interfering with the nulls. Can > someone help me with to create an update query that will accomplish this? > Thanks so much.
From: KARL DEWEY on 6 Apr 2010 17:07 Try this -- UPDATE TableA SET TableA.Countycode = 0 WHERE (((TableA.Countycode) Is Not Null)); Allways BACKUP DATABASE before doing gobal stuff. -- Build a little, test a little. "teddyb777" wrote: > I have a table that contains dollar amounts in random cells throughout the > table with the remainder of the cells containing nulls. I need to replace > every dollar amount with a zero while not interfering with the nulls. Can > someone help me with to create an update query that will accomplish this? > Thanks so much.
From: teddyb777 on 14 Apr 2010 13:32 Thanks John. This did exactly what I needed. Ted "John Spencer" wrote: > How many FIELDS are involved? > > UPDATE SomeTable > SET FieldA = [FieldA] * 0 > , FieldB = [FieldB] * 0 > > Null * 0 is Null > AnyNumberValue * 0 is Zero. > > If you have only a few records that have values you might be better off doing > one a field at a time (multiple queries - one for each field involved) and > filtering out the records where the field is Null. > > UPDATE SomeTable > SET FieldA = Null > WHERE FieldA is Not Null > > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > teddyb777 wrote: > > I have a table that contains dollar amounts in random cells throughout the > > table with the remainder of the cells containing nulls. I need to replace > > every dollar amount with a zero while not interfering with the nulls. Can > > someone help me with to create an update query that will accomplish this? > > Thanks so much. > . >
|
Pages: 1 Prev: Update Record Performance - Query vs Code Next: Cross tab query |