Complex IF statement

[PDLG]

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!

 

[Douglas J. Steele]

=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...

 

[PDLG]

=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)

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!!

 

[John W. Vinson]

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], < [X], , [C] < [X], [C], ..., True, "Nothing
qualified")

John W. Vinson [MVP]