Complex IF statement

I have a database and for one of my reports, I need to have Access
evaluate a series of fields and then display the value of one of the
fields based on specific criteria and I can't figure out how.

Here's the scenario:

Look at Field A and if there is a value less than X in there, display
this value in Field Z; if there is a value of X in there, skip to
Field B. If Field B has a value less than X in there, display this
value in Field Z; if there is a value of X in there, skip to Field
C . . . and so on.

There are about 10 fields and I've tried using an IF statement but,
this is too convoluted or . . . I just don't know how to write it.

Help!

=IIf([FieldA] X, [FieldA], IIf([FieldB] X, [FieldB], IIf([FIeldC] X,
[FieldC], ..., IIf([FieldJ] X, [FieldJ], Null))))))))))

I THINK you can nest 10 IIf statements like that...

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"PDLG" wrote in message
...
I have a database and for one of my reports, I need to have Access
evaluate a series of fields and then display the value of one of the
fields based on specific criteria and I can't figure out how.

Here's the scenario:

Look at Field A and if there is a value less than X in there, display
this value in Field Z; if there is a value of X in there, skip to
Field B. If Field B has a value less than X in there, display this
value in Field Z; if there is a value of X in there, skip to Field
C . . . and so on.

There are about 10 fields and I've tried using an IF statement but,
this is too convoluted or . . . I just don't know how to write it.

Help!

On Jan 9, 2:21*pm, "Douglas J. Steele"
wrote:
=IIf([FieldA] X, [FieldA], IIf([FieldB] X, [FieldB], IIf([FIeldC] X,
[FieldC], ..., IIf([FieldJ] X, [FieldJ], Null))))))))))

I THINK you can nest 10 IIf statements like that...

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

"PDLG" wrote in message

...



I have a database and for one of my reports, I need to have Access
evaluate a series of fields and then display the value of one of the
fields based on specific criteria and I can't figure out how.

Here's the scenario:

Look at Field A and if there is a value less than X in there, display
this value in Field Z; if there is a value of X in there, skip to
Field B. If Field B has a value less than X in there, display this
value in Field Z; if there is a value of X in there, skip to Field
C . . . and so on.

There are about 10 fields and I've tried using an IF statement but,
this is too convoluted or . . . I just don't know how to write it.

Help!- Hide quoted text -

- Show quoted text -

Thanks, that was perfect. I guess I just wasn't being patient enough!!

On Wed, 9 Jan 2008 14:14:00 -0800 (PST), PDLG wrote:

I have a database and for one of my reports, I need to have Access
evaluate a series of fields and then display the value of one of the
fields based on specific criteria and I can't figure out how.

Here's the scenario:

Look at Field A and if there is a value less than X in there, display
this value in Field Z; if there is a value of X in there, skip to
Field B. If Field B has a value less than X in there, display this
value in Field Z; if there is a value of X in there, skip to Field
C . . . and so on.

There are about 10 fields and I've tried using an IF statement but,
this is too convoluted or . . . I just don't know how to write it.

Help!

The Switch() function is prefect for this case. It takes an arbitrary number
of pairs of arguments; evaluates them left to right; and when it first
encounters a pair for which the first element is TRUE it returns the second of
the pair and quits:

Z: Switch([A] [X], [A], [b] [X], [b], [C] [X], [C], ..., True, "Nothing
qualified")

John W. Vinson [MVP]