IIF STATEMENT IN UPDATE QUERY

[laura reid]

Hello,

I'm trying write an update query that evaluates 3 critiera. I want it to
evaluate the critieria in one field [PP] and then update the values in the
[CIV TYP] field. My logic is as follows

If the PP Field is like C*, K* D*, or A*, then I want the CIV TYP field to
fill with 202.
If the PP Field is like X*, then fill CIV TYP field with 110
If the PP Field is like B*, then fill CIV TYP field with 206

for all else leave as is.

So this is what i've come up with from researching the newsgroups, but it
receive a syntax error.

UPDATE [CURRENT DCPDS]

SET [CURRENT DCPDS].[CIV TYP] = IIF(([CURRENT DCPDS].PP) Like "C*" Or
([CURRENT DCPDS].PP) Like "K*" Or ([CURRENT DCPDS].PP) Like "D*" Or ([CURRENT
DCPDS].PP) Like "A*"),"202")

SET [CURRENT DCPDS].[CIV TYP] = IIF(([CURRENT DCPDS].PP) Like "X*", "110")

SET [CURRENT DCPDS].[CIV TYP] = IIF(([CURRENT DCPDS].PP) Like "B*" ,"206")

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

UPDATE [CURRENT DCPDS]

SET [CIV TYP] =
Switch([PP] Like "C*"
Or [PP] Like "K*"
Or [PP] Like "D*"
Or [PP] Like "A*", "202",
[PP] Like "X*", "110",
[PP] Like "B*", "206")

WHERE [PP] Like "C*"
Or [PP] Like "K*"
Or [PP] Like "D*"
Or [PP] Like "A*"
Or [PP] Like "X*"
OR [PP] Like "B*"

The Switch() function is a VBA function. Sea the VBA Help article
"Switch Function" for more info.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlWMAIechKqOuFEgEQL0oACg8VqUnfgejJs1J4HoHAgHy2Zbh9sAniks
OZq4JTWoS6RAdw4wYldpOeV2
=7hjR
-----END PGP SIGNATURE-----

 

[[MVP] S.Clark]

It is probably in your better interest to utilize several queries to perform
all of the different updates, if for no other purpose than debugging or
rollback. For example, if you have a logic error, attempting to locate it
would be near impossible with all of those IIF()'s triggering independently.

Create the 3 queries, add them to a macro, then call the macro when needed.

$0.02