Update Query

[74Stag]

Hello All

I am trying to write an update query which will set a field to one of two possible values. The value to be set depends on a string contained in the same table.

For example:

If the string = "String1" or "String2" then the value needs to be: Value1

But if the string = "String3" then the value needs to be: Value2

Is there a way of doing this in an update query?

 

[alvechurchdata]

The IIF function should do this for you. It takes three parameters. If the first parameter is Ture then IIF returns the second parameter, if not it returns the third:

IIF(otherfield="string1" or otherfield="string2", Value1, Value2)

By the way, what should happen if the otherfield isn't "String1", "String2", or "String3"?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[PHV]

Take a look at the IIf function:
UPDATE yourTable
SET yourField = IIf([string field]='String3',Value2,Value1)
WHERE [string field] In ('String1','String2','String3')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[74Stag]

Thanks guys.

If the string is not "String1", "String2" or "String3" then the field is just left as it is.

Can anyone recommend a good reference site for functions such as this?

 

[alvechurchdata]

Can anyone recommend a good reference site for functions such as this?

Help in Access 97 has the useful "See Also" feature which leads you to functions like Choose() and Switch(). Don't know why it disappeared in Access 2000.

In later versions you might try opening the Expression Builder, selecting Functions, and looking at the list available. Iif(), Choose() and Switch() are under the "Program Flow" category.

Geoff Franklin

http://www.alvechurchdata.co.uk