|
| 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
December 26th, 2005, 05:56 PM
posted to microsoft.public.access
|
|||
|
|||
Update a Foeld based on another Field
I am trying to update a field based on another field using "Update Query" in
the same Table. An example is that I If a value of a certian field is "0" then I want to make another field in the same table "Null" Can anyone help? -- Thnaks! Dominic 
|
|
#2
December 26th, 2005, 06:03 PM
posted to microsoft.public.access
|
|||
|
|||
|
Update a Foeld based on another Field
Hi Dominic,
UPDATE [TableName] SET [FieldName] = Null WHERE [YesNoFieldName]=0; You might want to first verify that allow null is set to Yes in table design for FieldName. Tom http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Dominic" wrote: I am trying to update a field based on another field using "Update Query" in the same Table. An example is that I If a value of a certian field is "0" then I want to make another field in the same table "Null" Can anyone help? -- Thnaks! Dominic
|
|
#3
December 26th, 2005, 06:29 PM
posted to microsoft.public.access
|
|||
|
|||
|
Update a Foeld based on another Field
Tom,
Sorry for my dumbness I tried this formaula in the update To field and got an expression error on the "WHERE" I assume I have misunderstood! Also, what should be in the Field & Table row? -- Thanks! Dominic "Tom Wickerath" wrote: Hi Dominic, UPDATE [TableName] SET [FieldName] = Null WHERE [YesNoFieldName]=0; You might want to first verify that allow null is set to Yes in table design for FieldName. Tom http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Dominic" wrote: I am trying to update a field based on another field using "Update Query" in the same Table. An example is that I If a value of a certian field is "0" then I want to make another field in the same table "Null" Can anyone help? -- Thnaks! Dominic
|
|
#4
December 26th, 2005, 06:49 PM
posted to microsoft.public.access
|
|||
|
|||
|
Update a Foeld based on another Field
Hi Dominic,
The example I gave you is entered using the SQL View instead of the QBE (Query by example) view. In query design, click on View SQL View. Then copy the SQL statement I gave you and paste it into the SQL View. Make the appropriate changes for the names of the field and table, since my example just used generic names. You can then switch back to the QBE view, using the View menu. Tom http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Dominic" wrote: Tom, Sorry for my dumbness I tried this formaula in the update To field and got an expression error on the "WHERE" I assume I have misunderstood! Also, what should be in the Field & Table row? -- Thanks! Dominic __________________________________________ "Tom Wickerath" wrote: Hi Dominic, UPDATE [TableName] SET [FieldName] = Null WHERE [YesNoFieldName]=0; You might want to first verify that allow null is set to Yes in table design for FieldName. Tom http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Dominic" wrote: I am trying to update a field based on another field using "Update Query" in the same Table. An example is that I If a value of a certian field is "0" then I want to make another field in the same table "Null" Can anyone help? -- Thnaks! Dominic
|
|
#5
December 26th, 2005, 07:05 PM
posted to microsoft.public.access
|
|||
|
|||
|
Update a Foeld based on another Field
Tom,
Thnaks, that worked, one last question. What about more than one field: UPDATE [Directory List] SET [HomePhone] = Null WHERE [Print Phone Number]=0; UPDATE [Directory List] SET [CellPhone] = Null WHERE [Print Cell Number]=0; This didn't work, I ma sure it's not as simple as multiple line! Thanks again -- Thanks! Dominic "Tom Wickerath" wrote: Hi Dominic, The example I gave you is entered using the SQL View instead of the QBE (Query by example) view. In query design, click on View SQL View. Then copy the SQL statement I gave you and paste it into the SQL View. Make the appropriate changes for the names of the field and table, since my example just used generic names. You can then switch back to the QBE view, using the View menu. Tom http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Dominic" wrote: Tom, Sorry for my dumbness I tried this formaula in the update To field and got an expression error on the "WHERE" I assume I have misunderstood! Also, what should be in the Field & Table row? -- Thanks! Dominic __________________________________________ "Tom Wickerath" wrote: Hi Dominic, UPDATE [TableName] SET [FieldName] = Null WHERE [YesNoFieldName]=0; You might want to first verify that allow null is set to Yes in table design for FieldName. Tom http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Dominic" wrote: I am trying to update a field based on another field using "Update Query" in the same Table. An example is that I If a value of a certian field is "0" then I want to make another field in the same table "Null" Can anyone help? -- Thnaks! Dominic
|
|
#6
December 26th, 2005, 07:26 PM
posted to microsoft.public.access
|
|||
|
|||
|
Update a Foeld based on another Field
Hi Dominic,
Try this: UPDATE [Directory List] SET [HomePhone] = Null, [CellPhone] = Null WHERE [Print Phone Number]=0 OR [Print Cell Number]=0; Note that you cannot have repeats in keywords such as UPDATE, SET and WHERE; these can occur only once in a SQL statement (not including a special case of Union queries). Tom http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Dominic" wrote: Tom, Thnaks, that worked, one last question. What about more than one field: UPDATE [Directory List] SET [HomePhone] = Null WHERE [Print Phone Number]=0; UPDATE [Directory List] SET [CellPhone] = Null WHERE [Print Cell Number]=0; This didn't work, I ma sure it's not as simple as multiple line! Thanks again -- Thanks! Dominic
|
|
#7
December 26th, 2005, 07:30 PM
posted to microsoft.public.access
|
|||
|
|||
|
Update a Foeld based on another Field
Oops...hold on. Don't run that example yet!
Tom http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Tom Wickerath" wrote: Hi Dominic, Try this: UPDATE [Directory List] SET [HomePhone] = Null, [CellPhone] = Null WHERE [Print Phone Number]=0 OR [Print Cell Number]=0; Note that you cannot have repeats in keywords such as UPDATE, SET and WHERE; these can occur only once in a SQL statement (not including a special case of Union queries). Tom http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Dominic" wrote: Tom, Thnaks, that worked, one last question. What about more than one field: UPDATE [Directory List] SET [HomePhone] = Null WHERE [Print Phone Number]=0; UPDATE [Directory List] SET [CellPhone] = Null WHERE [Print Cell Number]=0; This didn't work, I ma sure it's not as simple as multiple line! Thanks again -- Thanks! Dominic
|
|
#8
December 26th, 2005, 07:37 PM
posted to microsoft.public.access
|
|||
|
|||
|
Update a Foeld based on another Field
Hi Dominic,
I think you are going to have to create two separate update queries. The example I gave you sets both fields to null if either condition was true. You probably don't want that. Tom http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Tom Wickerath" wrote: Oops...hold on. Don't run that example yet!
|
|
#9
December 26th, 2005, 07:47 PM
posted to microsoft.public.access
|
|||
|
|||
|
Update a Foeld based on another Field
On Mon, 26 Dec 2005 11:05:02 -0800, "Dominic"
wrote: Tom, Thnaks, that worked, one last question. What about more than one field: UPDATE [Directory List] SET [HomePhone] = Null WHERE [Print Phone Number]=0; UPDATE [Directory List] SET [CellPhone] = Null WHERE [Print Cell Number]=0; This didn't work, I ma sure it's not as simple as multiple line! It is. Unlike T-SQL or Oracle SQL procedures, you can't run multiple SQL queries together - each must be separate. John W. Vinson[MVP]
|
|
#10
December 29th, 2005, 12:46 PM
posted to microsoft.public.access
|
|||
|
|||
|
Update a Foeld based on another Field
"Tom Wickerath" AOS168b AT comcast DOT net ... Hi Dominic, Try this: UPDATE [Directory List] SET [HomePhone] = Null, [CellPhone] = Null WHERE [Print Phone Number]=0 OR [Print Cell Number]=0; Note that you cannot have repeats in keywords such as UPDATE, SET and WHERE; these can occur only once in a SQL statement (not including a special case of Union queries). Tom http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Dominic" wrote: Tom, Thnaks, that worked, one last question. What about more than one field: UPDATE [Directory List] SET [HomePhone] = Null WHERE [Print Phone Number]=0; UPDATE [Directory List] SET [CellPhone] = Null WHERE [Print Cell Number]=0; This didn't work, I ma sure it's not as simple as multiple line! Thanks again -- Thanks! Dominic up
|
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| How To:? Update One Field Based On Another Field | TheHouse | General Discussion | 4 | October 11th, 2005 09:28 PM |
| Update a field in a query based on a user input | RT | Running & Setting Up Queries | 4 | July 6th, 2005 09:10 PM |
| Update null field based on certain data in comment field | JCarter | Running & Setting Up Queries | 3 | May 7th, 2005 12:39 AM |
| Syntax needed to get needed reports | Frank Lueder | New Users | 15 | January 6th, 2005 08:39 AM |
| Update field based on list box choices | Caroline | Using Forms | 2 | September 9th, 2004 07:17 PM |
Linear Mode
