Criteria

  • Thread starter Thread starter Xinwa Chang
  • Start date Start date
X

Xinwa Chang

I have a table with 3 columns - a1, a2, a3 and and a3 is
blank and there are 10,000 records in the table. I want
to use IF-THEN -ELSE in Criteria to update 3 records(only
update 3 records)in column a3 and rest a3 keep blank. I
write the following in Criteria of a3:

if a1="a" and a2="b" then a3="updateme_a"
else if a1="c" and a2="d" then a3="updateme_c"
else if a1="e" and a2="e" then a3="updateme_e"
else a3=""
end if

Why it does't work? Can I use IF-THEN-ELSE in Criteria?

Thanks,

Xinwa Chang
 
Where are you putting this criteria? You need to build an updat query to
modify the data in a table.

Rick B


I have a table with 3 columns - a1, a2, a3 and and a3 is
blank and there are 10,000 records in the table. I want
to use IF-THEN -ELSE in Criteria to update 3 records(only
update 3 records)in column a3 and rest a3 keep blank. I
write the following in Criteria of a3:

if a1="a" and a2="b" then a3="updateme_a"
else if a1="c" and a2="d" then a3="updateme_c"
else if a1="e" and a2="e" then a3="updateme_e"
else a3=""
end if

Why it does't work? Can I use IF-THEN-ELSE in Criteria?

Thanks,

Xinwa Chang
 
Criteria has to evaluate to True or False. An IF-THEN construct might work,
but only if it returned one of those values for a specific field (not
combination of fields). In any case, you can't set the UPDATETO value via
the Criteria. Criteria and UpdateTo are entirely separate entities that you
can't mix together.

Do your UPDATE query 3 times (I assume you are trying to do an UPDATE query
in Query design view)
#1 A1Criteria:="a", A2Criteria: = "b", A3UpdateTo: "updateme_a"
#2 A1Criteria:="c", A2Criteria: = "d", A3UpdateTo: "updateme_c"
#3 A1Criteria:="e", A2Criteria: = "e", A3UpdateTo: "updateme_e"

And a fourth time if you want to force any remaining Null values to empty
strings:
A3Criteria: Is Null, A3UpdateTo: ""
 
Back
Top