how to do a conditional update

[vc-programmer]

hello all,

I have a table "mytable", from which "date1" holds a date and "label" can
contain 2 different text values ( day "one" and "two" ), and a field "result"
which can hold a date.

what I want to do, but not sure if I can is to use a condition to execute
either of two update queries which is logically:
IIf( mytable.label="one", <update = date1>, <update = 04/01/08>)

my update queries work fine when used individually, but not when I both it
in the conditional IIf. the update queries are :

UPDATE [mytable] SET [mytable].[result] = [mytable].[date1]
WHERE ((([mytable].label)="one"));
and
UPDATE [mytable] SET [mytable].[result] = #04/01/08#
WHERE ((([mytable].label)="two"));

not sure what i am doing wrong.

thanks.

 

[Klatuu]

You can do both with one query:
UPDATE [mytable] SET [mytable].[result] = IIf([mytable].label)="one",
[mytable].[date1], #04/01/08#);

Now, what you don't address is what to do if labe is something other than
one or two.

 

[vc-programmer]

thank you dave, that answers my original questions so I have marked that as
the answer.

Now, what you don't address is what to do if labe is something other than
one or two.

i guess if I have something more that one or two i can use a nested iff, but
i guess thats not the best way to do it.

i have one more question : i have a few update queries similar to the above,
some of which which perform the same operation on a different table. is there
a way to perform a multiple table update in a single query

thanks again

You can do both with one query:
UPDATE [mytable] SET [mytable].[result] = IIf([mytable].label)="one",
[mytable].[date1], #04/01/08#);

Now, what you don't address is what to do if labe is something other than
one or two.
--
Dave Hargis, Microsoft Access MVP

hello all,

I have a table "mytable", from which "date1" holds a date and "label" can
contain 2 different text values ( day "one" and "two" ), and a field "result"
which can hold a date.

what I want to do, but not sure if I can is to use a condition to execute
either of two update queries which is logically:
IIf( mytable.label="one", <update = date1>, <update = 04/01/08>)

my update queries work fine when used individually, but not when I both it
in the conditional IIf. the update queries are :

UPDATE [mytable] SET [mytable].[result] = [mytable].[date1]
WHERE ((([mytable].label)="one"));
and
UPDATE [mytable] SET [mytable].[result] = #04/01/08#
WHERE ((([mytable].label)="two"));

not sure what i am doing wrong.

thanks.

 

[Klatuu]

A nested IIf would be a reasonable way, if there weren't too many choices;
otherwise, it gets really hard to read.

No, you need a seperate query for each table you want to update.
If you want to run multiple queries in succession, you could put them all in
a macro, or you could do it in VBA.
--
Dave Hargis, Microsoft Access MVP

thank you dave, that answers my original questions so I have marked that as
the answer.

Now, what you don't address is what to do if labe is something other than
one or two.

i guess if I have something more that one or two i can use a nested iff, but
i guess thats not the best way to do it.

i have one more question : i have a few update queries similar to the above,
some of which which perform the same operation on a different table. is there
a way to perform a multiple table update in a single query

thanks again

You can do both with one query:
UPDATE [mytable] SET [mytable].[result] = IIf([mytable].label)="one",
[mytable].[date1], #04/01/08#);

Now, what you don't address is what to do if labe is something other than
one or two.
--
Dave Hargis, Microsoft Access MVP

hello all,

I have a table "mytable", from which "date1" holds a date and "label" can
contain 2 different text values ( day "one" and "two" ), and a field "result"
which can hold a date.

what I want to do, but not sure if I can is to use a condition to execute
either of two update queries which is logically:
IIf( mytable.label="one", <update = date1>, <update = 04/01/08>)

my update queries work fine when used individually, but not when I both it
in the conditional IIf. the update queries are :

UPDATE [mytable] SET [mytable].[result] = [mytable].[date1]
WHERE ((([mytable].label)="one"));
and
UPDATE [mytable] SET [mytable].[result] = #04/01/08#
WHERE ((([mytable].label)="two"));

not sure what i am doing wrong.

thanks.