Fred and RG - that worked - thank you! Fred your number 2 was what worked.
So now I have a follow up question:
I have 3 text boxes with conditional formatting to turn red when I want to
show the error, but I really want it to be just one text box.
Text Box 1
=IIf([Term of Assignment]=3 And [lapsed time of assign]>=1,"ERROR! These
Assignments are for less than 1 yr","")
Text Box 2
=IIf([Term of Assignment]=2 And ([lapsed time of assign]<1 Or [lapsed time
of assign]>=3),"ERROR! These Assignments are for more than 1 but less than 3
yrs","")
Text Box 3
=IIf([Term of Assignment]=1 And [lapsed time of assign]<3,"ERROR! These
Assignments are for more than 3 yrs","")
Isn't there some way I could combine all of them? I tried actually placing
them as transparent on top of eachother, then the conditional formatting in
red doesn't happen.
fredg said:
I'm trying to see if a field is between a certain value, and give an error
message, but this isn't working. Is there a between comand or something
that I'm missing?
Sorry if this question is basic!!
=IIf([Term of Assignment]=2 And [lapsed time of assign] >3 or < 1,"ERROR!
Long Term Assignments are for more than 3 yrs","")
Thank you!
Varvara
You have to repeat the criteria field each time.
1)
=IIf([Term of Assignment]=2 And [lapsed time of assign] >3 or [lapsed
time of assign] < 1,"ERROR! Long Term Assignments are for more than 3
yrs","")
Note: You may need to group some of the criteria within parentheses as
you may not get the results you expect as the above is written. I have
no idea how to group your criteria for you.
2)
=IIf([Term of Assignment]=2 And ([lapsed time of assign] >3 or [lapsed
time of assign] < 1),"ERROR! Long Term Assignments are for more than 3
yrs","")
or possibly:
3)
=IIf(([Term of Assignment]=2 And [lapsed time of assign] >3) or
[lapsed time of assign] < 1,"ERROR! Long Term Assignments are for more
than 3 yrs","")
The 3 different versions may return different results.
