If....Else in Queries

[Arpan]

Don't Queries in MS-Access support If....Else conditions? Please note I
am not referring to modules or VBA code. I am referring to Queries.

I couldn't find anything other than IIF. My primary intention is to
execute SQL DML statements within If...Else conditions something like
this:

If (condition is True)
UPDATE MyTable SET Col1 = "ABCD"
Else
UPDATE MyTable SET Col2 = "WXYZ"

How do I accomplish this?

 

[Rick Brandt]

Don't Queries in MS-Access support If....Else conditions? Please note
I am not referring to modules or VBA code. I am referring to Queries.

I couldn't find anything other than IIF. My primary intention is to
execute SQL DML statements within If...Else conditions something like
this:

If (condition is True)
UPDATE MyTable SET Col1 = "ABCD"
Else
UPDATE MyTable SET Col2 = "WXYZ"

How do I accomplish this?

Only in VBA code. An Access query can only execute one statement and there
are no branching or conditionals like what you are looking for.

 

[Rick Brandt]

Only in VBA code. An Access query can only execute one statement and
there are no branching or conditionals like what you are looking for.

Actually I can qualify that. You cannot conditionally execute two
completely different statements like you have in your example. Hoewever
your example is updating the same table (just different columns) so you
could use something like...

UPDATE MyTable
SET Col1 = IIf(condition is True, "ABCD", Col1),
Col2 = IIf(condition is True, Col2, "WXYZ")

....where depending on the outcome of the test either Col1 is updated or Col2
is updated. You actually always update both columns, but depending on the
test one column is just being updated to itself.

 

[Guest]

The only way you could do this would entirely in the update query be to
update both columns, updating to the return value of separate IIF function
calls for each so that the column updates to a new value if a condition is
met, and to its current value if not.

The alternative is to update each column to the return value of a custom VBA
function which returns either the new value or the current value of the
column. You'd have to pass the current value into the function and whatever
value(s) the function needs to determine what value to return.

Whichever is the most appropriate solution depends on the complexity of the
condition.

Ken Sheridan
Stafford, England

 

[Arpan]

Thanks Rick for your suggestion.

What if I want that if the condition turns out to be False, I did like
to only SELECT some records from the table & not UPDATE any record but
if the condition is True, then only I would like to update a record in
the table?

That won't be possible, isn't it?

 

[Rick Brandt]

Thanks Rick for your suggestion.

What if I want that if the condition turns out to be False, I did like
to only SELECT some records from the table & not UPDATE any record but
if the condition is True, then only I would like to update a record in
the table?

That won't be possible, isn't it?

Not with JUST a query no, but it would be very easy with a couple lines of code.
Any reason you don't want to use that?