Need help with an expression, please

[Elizabeth]

I have a form with an unbound control in which I want to
display information depending on a value in the
underlying table. I can't get the expression to do what I
want.

In English:
If PriorDisposition is "Rejected", say "It was rejected".
If PriorDisposition is "Incident", say "It was an
incident". If PriorDispostion = "Assigned", don't say
anything at all.

I've tried the following and it works, but it doesn't
handle the "Assigned" argument.

=IIf([PriorDisposition]="Rejected" Or "Incident" And
Not "Assigned","It was rejected ","It was an Incident")

I tried...
=IIf Not([PriorDisposition]="Assigned" And
[PriorDisposition] = "Rejected" Or "Incident" And
Not "Assigned","It was rejected ","It was an Incident")

....and it "definately" does not like that!

How should this code be modified? Alternatively, would it
be better to code this when the form opens? If so, where?
I know how to write If..Else..Otherwise and Select Case
statements, but no matter where I put the code, like in
the form's On Open event, it doesn't work.

Many thanks to helpful folks,
Elizabeth

 

[Van T. Dinh]

Try:

=IIf([PriorDisposition] = "Rejected", "It was rejected",
IIf([PriorDisposition] = "Incident", "It was an incident",
""))

Basically, you use a nested Iif construct if you need more
than 2 outcomes.

HTH
Van T. Dinh
MVP (Access)

 

[Elizabeth]

Many thanks, Van - I didn't realise you could nest IIF
statements!
Elizabeth

-----Original Message-----
Try:

=IIf([PriorDisposition] = "Rejected", "It was rejected",
IIf([PriorDisposition] = "Incident", "It was an incident",
""))

Basically, you use a nested Iif construct if you need more
than 2 outcomes.

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
I have a form with an unbound control in which I want to
display information depending on a value in the
underlying table. I can't get the expression to do what I
want.

In English:
If PriorDisposition is "Rejected", say "It was rejected".
If PriorDisposition is "Incident", say "It was an
incident". If PriorDispostion = "Assigned", don't say
anything at all.

I've tried the following and it works, but it doesn't
handle the "Assigned" argument.

=IIf([PriorDisposition]="Rejected" Or "Incident" And
Not "Assigned","It was rejected ","It was an Incident")

I tried...
=IIf Not([PriorDisposition]="Assigned" And
[PriorDisposition] = "Rejected" Or "Incident" And
Not "Assigned","It was rejected ","It was an Incident")

....and it "definately" does not like that!

How should this code be modified? Alternatively, would it
be better to code this when the form opens? If so, where?
I know how to write If..Else..Otherwise and Select Case
statements, but no matter where I put the code, like in
the form's On Open event, it doesn't work.

Many thanks to helpful folks,
Elizabeth
.

.

 

[Elizabeth]

Van, one other quick question....

If I want to add a date to the expression, how do you get
it to format the way you want?

=IIf([PriorDisposition] = "Rejected", "It was rejected
on ", & [PriorDispositionDate]

The source field (PriorDispositionDate) has its format
set to Medium Date (14-Feb-04), but the expression output
displays 14/02/2004. Access Expression Help doesn't have
an option for handling this.

Thanks again,
Elizabeth