Help Null in update statement

  • Thread starter Thread starter SqlDope
  • Start date Start date
S

SqlDope

Hello, This is not to do with forms, but I dont know where else to post.
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
 
not tested:

UPDATE Table SET AllClassrooms = IIF(ISNULL(Classroom1 ),"","ClassRm1 " &
Classroom1) & IIF(ISNULL(Classroom2 ), ""," ClassRm2 " & Classroom2)
 
Probably okay except you should is IS NULL in SQL rather than IsNull which is
a VBA function

UPDATE Table SET AllClassrooms = IIF(Classroom1 IS NULL,"","ClassRm1 " &
Classroom1) & IIF(Classroom2 IS NULL, ""," ClassRm2 " & Classroom2)
 
Hello,
Many Thanks David And Klatuu
Will Test the code tomorrow, It Is going on Midnight here in UK
Many thanks again and best regards
 
Back
Top