Precedence of one condition over the other

[Guest]

I am using a statement creating a calculated field based on values in two
fields of a query, [Fld1] and [Life] as below:
New Field: IIf([Fld1]="Val1*","ABC",Switch([Life]>=0 And [Life]<=30,"<30
days",[Life]>30 And [Life]<=60,"31-60 days",[Life]>60 And [Life]<=90,"61-90
days",[Life]>90 And [Life]<=120," 91-120 days",[Life]>120,">=120 days"))

What I am trying to achieve is that "New Field" should assume the value
"ABC" if [Fld1] = Val1* regardless of the value in [Life]. What is happening
with the above statement is that for rows which have both [Fld1] = Val1* and
also meeting one of the conditions for [Life], the new field asumes the value
from the switch statement and not "ABC" as I would like it to. I would like
[Fld1] = Val1 to take precedence over the [Life] condition. How can I do it?

 

[[MVP] S.Clark]

Yikes.

Create a table to store all the junk you have in this statement. Like this
table to whatever data table you have.

Break the process up from there. Do more than one query if you have to.

 

[Guest]

I dropped the iif to make it look like this:
New Field: New Field: Switch([Life]>=0 And [Life]<=30,"<30

days",[Life]>30 And [Life]<=60,"31-60 days",...)

Now I am trying to make an update query which which will update the "New
Field" to "ABC" when the criteria of [Fld1] = "Val1" is met. But when I try
to execute it, it says, "Operation must use an updateable query" ?

Yikes.

Create a table to store all the junk you have in this statement. Like this
table to whatever data table you have.

Break the process up from there. Do more than one query if you have to.

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using a statement creating a calculated field based on values in two
fields of a query, [Fld1] and [Life] as below:
New Field: IIf([Fld1]="Val1*","ABC",Switch([Life]>=0 And [Life]<=30,"<30
days",[Life]>30 And [Life]<=60,"31-60 days",[Life]>60 And
[Life]<=90,"61-90
days",[Life]>90 And [Life]<=120," 91-120 days",[Life]>120,">=120 days"))

What I am trying to achieve is that "New Field" should assume the value
"ABC" if [Fld1] = Val1* regardless of the value in [Life]. What is
happening
with the above statement is that for rows which have both [Fld1] = Val1*
and
also meeting one of the conditions for [Life], the new field asumes the
value
from the switch statement and not "ABC" as I would like it to. I would
like
[Fld1] = Val1 to take precedence over the [Life] condition. How can I do
it?

 

[[MVP] S.Clark]

Switch isn't that much better than IIF(), when used within a query such as
this... IMHO. Store the data in tables then LINK it, instead of doing this
on-the-fly parsing.

The non-updateable query error is very common. You may have to store the
data to an intermediate table, then perform the update. It's a real PITA
sometimes.

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I dropped the iif to make it look like this:
New Field: New Field: Switch([Life]>=0 And [Life]<=30,"<30

days",[Life]>30 And [Life]<=60,"31-60 days",...)

Now I am trying to make an update query which which will update the "New
Field" to "ABC" when the criteria of [Fld1] = "Val1" is met. But when I
try
to execute it, it says, "Operation must use an updateable query" ?

Yikes.

Create a table to store all the junk you have in this statement. Like
this
table to whatever data table you have.

Break the process up from there. Do more than one query if you have to.

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I am using a statement creating a calculated field based on values in
two
fields of a query, [Fld1] and [Life] as below:
New Field: IIf([Fld1]="Val1*","ABC",Switch([Life]>=0 And
[Life]<=30,"<30
days",[Life]>30 And [Life]<=60,"31-60 days",[Life]>60 And
[Life]<=90,"61-90
days",[Life]>90 And [Life]<=120," 91-120 days",[Life]>120,">=120
days"))

What I am trying to achieve is that "New Field" should assume the value
"ABC" if [Fld1] = Val1* regardless of the value in [Life]. What is
happening
with the above statement is that for rows which have both [Fld1] =
Val1*
and
also meeting one of the conditions for [Life], the new field asumes the
value
from the switch statement and not "ABC" as I would like it to. I would
like
[Fld1] = Val1 to take precedence over the [Life] condition. How can I
do
it?