Help Null UPdate

S

SqlDope

Hello, I need some help with a SQL statement in Access 2003
Existing Table = Table
Existing Fields = Clasroom1 ,Classroom2, AllClassrooms
I wish to do this:
UPDATE Table SET AllClassrooms = "ClassRm1 " & Classroom1 WHERE Classroom1
NOT NULL
& " ClassRm2 " & Classroom2 WHERE Classroom2 NOT NULL


Data
Classroom1 Classroom2
10
20
20 40


Desired Result

Classroom1 Classroom2 AllClassrooms
10 ClassRm1 10
20 ClassRm2 20
30 40 ClassRm1 30 ClassRm2 40

In Other words I want AllClassrooms field to contain data from Classroom1
and Classroom2
except where there is no data in those fields.
I Guess I shoud be using AND somewhere here

This works but I get the words ClassRm1 and ClassRm2 but (correctly) with no
data

UPDATE Table SET AllClassrooms = "ClassRm1 " & Classroom1 &
& " ClassRm2 " & Classroom2
Result
Classroom1 Classroom2 AllClassrooms
10 ClassRm1 10 ClassRm2
20 ClassRm1 ClassRm2 20
30 40 ClassRm1 30 ClassRm2 40

Any Help Appreciated
Many Thanks And Best Regards
 
M

Marshall Barton

SqlDope said:
Hello, I need some help with a SQL statement in Access 2003
Existing Table = Table
Existing Fields = Clasroom1 ,Classroom2, AllClassrooms
I wish to do this:
UPDATE Table SET AllClassrooms = "ClassRm1 " & Classroom1 WHERE Classroom1
NOT NULL
& " ClassRm2 " & Classroom2 WHERE Classroom2 NOT NULL


Data
Classroom1 Classroom2
10
20
20 40


Desired Result

Classroom1 Classroom2 AllClassrooms
10 ClassRm1 10
20 ClassRm2 20
30 40 ClassRm1 30 ClassRm2 40

In Other words I want AllClassrooms field to contain data from Classroom1
and Classroom2
except where there is no data in those fields.
I Guess I shoud be using AND somewhere here

This works but I get the words ClassRm1 and ClassRm2 but (correctly) with no
data

UPDATE Table SET AllClassrooms = "ClassRm1 " & Classroom1 &
& " ClassRm2 " & Classroom2
Result
Classroom1 Classroom2 AllClassrooms
10 ClassRm1 10 ClassRm2
20 ClassRm1 ClassRm2 20
30 40 ClassRm1 30 ClassRm2 40
Click to expand...


You can use the IIf function to do that:

UPDATE Table SET AllClassrooms = IIf(Classroom1 Is Not Null,
"ClassRm1 " & Classroom1, "") & IIf(Classroom1 + Classroom2
Is not Null, " ","") & IIf(Classroom2 Is Not Null,
"ClassRm2 " & Classroom2, "")

Or a little shorter:

UPDATE Table SET AllClassrooms = ("ClassRm1 " + Classroom1)
& IIf(Classroom1 + Classroom2 Is not Null, " ","") &
("ClassRm2 " + Classroom2)
 
Top