If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Complex IF statement
=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! |
#3
|
|||
|
|||
Complex IF statement
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!! |
#4
|
|||
|
|||
Complex IF statement
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] |
Thread Tools | |
Display Modes | |
|
|