Using case function in update query

[KimmieB]

Is it possible to use a Case function in a access query? I'm trying to create a update query and I'd like to use a Case function to set the value I'm updating based on another column in the same table. So if I have a table like this:

Row Value
1
2
3

I want to update the Value field with 0 if the row is 1 or 2 but set it to Null if the row is 3.

 

[jebry]

Hi!

You can't use the Select Case in SQL. You can do this with a nested IIf or, if there are too many values for a nested IIf, then write a public function and call it from the query:

Public MyFunction(YourField As Integer) As Integer

Select Case YourField
Case 1, 2
MyFunction = 0
Case etc.
End Select

End Function

In the query

MyField: MyFunction(YourField)

or in an update query:

Update to MyFunction(YourField)

hth


Jeff Bridgham
bridgham@purdue.edu

 

[PHV]

UPDATE yourTable
SET [Value] = IIf(Row=1 Or Row=2, 0, Null)
WHERE Row In (1,2,3);

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