Combine two fields, Null fields involved

[yes sir]

Title may not make much sense, so if you have come this far then stick with me.



I want to combine two fields for a form and probably on a couple of reports as well. I want a comma to show if both fields contain a value, however, if only one of the fields has a value and the other does not, then I do not want a comma.

For example, there are college athletes that have played some international ball before playing collegiality.

Lets say I have a field called 'College' and another as 'International Club.'

Obviously, I know I can display this as Team: [College] & ", " & [International Club]. However, by doing this, for any player with just International experience and no College, their field will display as ", International Club."

To sum things up, I just want a comma to show when it is necessary.

[RuralGuy]

The "+" instead of the "&" will *almost* do what you want. The "&" is the accepted concatenation character but the plus sign "+" will propagate the null field and the "&" will not. You may need to make this a two stage operation because you cannot just replace the "&" with a "+" since you do not know which field may be Null. If you replace all of the "&" with "+" then any null will wipe out any valid entry. IHTH's.

[yes sir]

The "+" instead of the "&" will *almost* do what you want. The "&" is the accepted concatenation character but the plus sign "+" will propagate the null field and the "&" will not. You may need to make this a two stage operation because you cannot just replace the "&" with a "+" since you do not know which field may be Null. If you replace all of the "&" with "+" then any null will wipe out any valid entry. IHTH's.

Yeah, I switched the & to a + earlier, and then any merged label that didn't contain a value in each field was just completely wiped out and was empty. Only the label that had both fields would display.

[yes sir]

I don't quite understand your two stage operation statement.

[yes sir]

Damn, that was easy.

I changed over to this Team: [College] & ", " + [International Club]

As simple as that was, this is pretty freaking awesome.

[yes sir]

Damn, that was easy.

I changed over to this Team: [College] & ", " + [International Club]

As simple as that was, this is pretty freaking awesome.

DENIED

Now if there is a only an International Club it shows as ", International Club"

I got a little too excited. Whoops. Back to the drawing board.

[yes sir]

I have tried using an expression like this:

=IIf(IsNull([College]) or [College]="","",[College])
& IIf(IsNull([International Club]) or [International Club]="","",(", " & [International Club]))

However, if there is an instance where a player has an International Club and NOT College, then the comma still shows. ", International Club"

There really seems to be no way around this.

[RuralGuy]

Try: AIR CODE

=IIf(Len([College]) And Len([International Club]), _
      [College] & "," & [International Club], _
      IIf(Len([College]), _
         [College], _
         IIf(Len([International Club]), _
            [International Club], "")))

[yes sir]

Per ajetrumpet:

Team: IIf([School] Is Null,IIf([International Club] Is Null,"",[International Club]),IIf([International Club] Is Null,[School],[School] & ", " & [International Club]))

Works just the way I want.

Returns the three possible values the way I want, "School" "International Club" "School, International Club"

[RuralGuy]

Excellent! Thanks for posting back with your success.