update query

[andrewm]

sorry I am new to update queries

I would like to change a 2 different fields depending on two different criteria in another field.
in this table - in a field called name
if it is " andrew " I want a field called monday to be updated to " yes "
if it is " John " I want a different field called tuesday to be updated to "yes"

I tried this on one update query and it didn't work

thanks

andrewm

 

[neileg]

if it is " andrew " I want a field called monday to be updated to " yes "
if it is " John " I want a different field called tuesday to be updated to "yes"
In the UPDATE TO section for monday put this

IIf([Name]="andrew",-1,0)

And for tuesday

IIf([Name]="John",-1,0)

-1 is the value for yes and 0 the value for no in a tickbox field. If these fields are text fields, substitute "yes" and "no" for the values.

By the way, Name is a bad word to use as a fieldname, as it is a reserved word in VBA and may cause problems.

 

[andrewm]

thankyou

many thanks

andrewm

 

[Pat Hartman]

Combining these two separate updates in a single query requires that you define what the update is when the field is NOT the specified value. neileg assumed that if the value is not andrew, you want monday to be no and if the field is not John, you want tuesday to be no. If that is the case, the posted suggestion is correct. If you want the field value to be what it was originally, change the IIf()'s else condition.

In the UPDATE TO section for monday put this

IIf([FirstName]="andrew",-1, Monday)

And for tuesday

IIf([FirstName]="John",-1,Tuesday)

I also changed the field name from "Name" to "FirstName" because I don't want you to think for a minute that "Name" is an acceptable name for a table column. Name is a property and if you need to use VBA at all, this is one of those column names that will cause problems. Avoid using function or property names or other reserved words as your column names.