update query?

[QB]

I don't know if this should be a vba question or if it can be done using a
simple query, but...

I need to go through a table (specific columns) and if they are null then
set the value to 0. I am hoping I can perform this action on all the
concerned columns in 1 query (I only seem to be able to do it by creating a
query for each column which is very cumbersome).

Table: HR
Columns to be checked: ProgHrsEst, ProgHrsAct, AssHrsEst, AssHrsAct,
TestHrsEst, TestHrsAct

Thank you for your guidance.

QB

 

[Bob Barrows]

I don't know if this should be a vba question or if it can be done
using a simple query, but...

I need to go through a table (specific columns) and if they are null
then set the value to 0. I am hoping I can perform this action on
all the concerned columns in 1 query (I only seem to be able to do it
by creating a query for each column which is very cumbersome).

Table: HR
Columns to be checked: ProgHrsEst, ProgHrsAct, AssHrsEst, AssHrsAct,
TestHrsEst, TestHrsAct

Thank you for your guidance.

QB

You can use the Nz function to accomplish this. The sql would look like
this:
update

---

Set [ProgHrsEst] = Nz([ProgHrsEst],0),
[ProgHrsAct] = Nz([ProgHrsAct],0),
etc.

Switch a query to SQL View to input this sql statement, then switch back
to Design to see how it should be done in the grid.

 

[John Spencer MVP]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

UPDATE HR
SET ProgHrsEst = Nz([ProgHrsEst],0)
, ProgHrsAct = Nz([ProgHoursAct],0)
, AssHrsEst = Nz([AssHrsEst],0)
, AssHrsAct = ...
, TestHrsEst = ...
, TestHrsAct = ...
WHERE ProgHrsEst Is Null Or ProgHrsAct is Null Or AssHrsEst is Null Or
AssHrsAct is Null or TestHrsEst is Null or TestHrsAct is Null

If you only know how to do this using the query design view then your update
to under each field should read like the following - obviously replacing the
field name with the correct field name.
NZ(

---

.[ProgHrsEst],0)

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

 

[QB]

Worked like a charm. Thank you!

I don't know if this should be a vba question or if it can be done
using a simple query, but...

I need to go through a table (specific columns) and if they are null
then set the value to 0. I am hoping I can perform this action on
all the concerned columns in 1 query (I only seem to be able to do it
by creating a query for each column which is very cumbersome).

Table: HR
Columns to be checked: ProgHrsEst, ProgHrsAct, AssHrsEst, AssHrsAct,
TestHrsEst, TestHrsAct

Thank you for your guidance.

QB

You can use the Nz function to accomplish this. The sql would look like
this:
update

---

Set [ProgHrsEst] = Nz([ProgHrsEst],0),
[ProgHrsAct] = Nz([ProgHrsAct],0),
etc.

Switch a query to SQL View to input this sql statement, then switch back
to Design to see how it should be done in the grid.

---