Update a Foeld based on another Field

[Guest]

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?

 

[Guest]

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/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

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?

 

[Guest]

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?

 

[Guest]

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/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

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
__________________________________________

:

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/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

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?

 

[Guest]

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

 

[Guest]

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/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

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

 

[Guest]

Oops...hold on. Don't run that example yet!


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

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/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Oops...hold on. Don't run that example yet!

 

[John Vinson]

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]

 

[hsy]

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/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

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

up

 

[MARQUER]

merci de ton emvoye mais je ne sais pas lire a 100/l'anglais donc si tu
désire m'ecrire écris, moi en francais merci bonne année 2006

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/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

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

up