|
| 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
October 4th, 2006, 10:42 PM
posted to microsoft.public.access.forms
|
|||
|
|||
Help Null in update statement
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 
|
|
#2
October 4th, 2006, 11:01 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Help Null in update statement
not tested:
UPDATE Table SET AllClassrooms = IIF(ISNULL(Classroom1 ),"","ClassRm1 " & Classroom1) & IIF(ISNULL(Classroom2 ), ""," ClassRm2 " & Classroom2) "SqlDope" wrote in message ... 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
|
|
#3
October 4th, 2006, 11:15 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Help Null in update statement
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) "David F Cox" wrote: not tested: UPDATE Table SET AllClassrooms = IIF(ISNULL(Classroom1 ),"","ClassRm1 " & Classroom1) & IIF(ISNULL(Classroom2 ), ""," ClassRm2 " & Classroom2) "SqlDope" wrote in message ... 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
|
|
#4
October 4th, 2006, 11:36 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Help Null in update statement
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 "David F Cox" wrote in message ... not tested: UPDATE Table SET AllClassrooms = IIF(ISNULL(Classroom1 ),"","ClassRm1 " & Classroom1) & IIF(ISNULL(Classroom2 ), ""," ClassRm2 " & Classroom2) "SqlDope" wrote in message ... 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
