Choose one value from a group of fields?

[tonyobrian]

I'm a definite beginner here, so please excuse me if this has a simple answer.

BACKSTORY
I work for a nonprofit and we are working to automate the gift acknowledgement process. I currently geta file that comes from the Oracle database where we record all of our gifts. The file is imported into Access 2003 and we run a variety of functions from the raw data -- one of the newer desires is to run actual letters. The letters are setup such that the report looks for the letter code in the query and then through a simple IIf statement displays the appropriate text for that letter code. The problem is in the salutation line of the letter (Dear Mr. Doe, where "Mr. Doe" is the field [FORMAL_SALUTATION]). There are 6 salutation fields that come on the Oracle download file -- formal, informal, combined, etc. that may or may not be populated.

DESIRED OUTCOME
I am trying to get the report to look for and display the "best" salutation based on what is available. So if there is data in the [COMBINED_INFORMAL_SALUTATION] field like "Jane and John," I'd want Access to pull that instead of [COMBINED_FORMAL_SALUTATION] -- "Mr. and Mrs. Doe" In other words, informal salutations sit higher on the heirarchy.

SO...
Is there a way to rank fields so that the report pulls only the top ranking populated field? I tried nesting IIf(IsNull statements, but the expression was getting ridiculously long and didn't even work correctly.

Am I trying to swim upstream here or is there some kind of answer?? Thanks to anyone who can help!

[ADezii]

I'm a definite beginner here, so please excuse me if this has a simple answer.

BACKSTORY
I work for a nonprofit and we are working to automate the gift acknowledgement process. I currently geta file that comes from the Oracle database where we record all of our gifts. The file is imported into Access 2003 and we run a variety of functions from the raw data -- one of the newer desires is to run actual letters. The letters are setup such that the report looks for the letter code in the query and then through a simple IIf statement displays the appropriate text for that letter code. The problem is in the salutation line of the letter (Dear Mr. Doe, where "Mr. Doe" is the field [FORMAL_SALUTATION]). There are 6 salutation fields that come on the Oracle download file -- formal, informal, combined, etc. that may or may not be populated.

DESIRED OUTCOME
I am trying to get the report to look for and display the "best" salutation based on what is available. So if there is data in the [COMBINED_INFORMAL_SALUTATION] field like "Jane and John," I'd want Access to pull that instead of [COMBINED_FORMAL_SALUTATION] -- "Mr. and Mrs. Doe" In other words, informal salutations sit higher on the heirarchy.

SO...
Is there a way to rank fields so that the report pulls only the top ranking populated field? I tried nesting IIf(IsNull statements, but the expression was getting ridiculously long and didn't even work correctly.

Am I trying to swim upstream here or is there some kind of answer?? Thanks to anyone who can help!

I'm not sure how you are implementing the Record Source in your Report, but the basic logic would be something like:

If Not IsNull(Me![Salutation_1]) Then
  'code to use Salutation_1
ElseIf Not IsNull(Me![Salutation_2]) Then
  'code to use Salutation_2
ElseIf Not IsNull(Me![Salutation_3]) Then
  'code to use Salutation_3
ElseIf Not IsNull(Me![Salutation_4]) Then
  'code to use Salutation_4
ElseIf Not IsNull(Me![Salutation_5]) Then
  'code to use Salutation_5
ElseIf Not IsNull(Me![Salutation_6]) Then
  'code to use Salutation_6
Else
  'catch all code
End If