Coding in Text box on a report

[PJ]

I have the following code in a text box on a report:

=IIf([Capital Markets Referral] Is Null,"",IIf([Capital Markets
Referral]="Agency","Agency",IIf([Capital Markets Referral]="Commercial Real
Estate","Commercial Real Estate",IIf([Capital Markets Referral]="Sub
Debt","Sub Debt" & Mid([Capital Markets Referral],InStr(1,[Capital Markets
Referral],",")+2,1) & Left([Capital Markets Referral],1)) & IIf([Capital
Markets Referral Secondary] Is Null,"","/" & Mid([Capital Markets Referral
Secondary],InStr(1,[Capital Markets Referral Secondary],",")+2,1) &
Left([Capital Markets Referral Secondary],1)) & IIf([Capital Markets Jr
Support] Is Null,"","/" & Mid([Capital Markets Jr Support],InStr(1,[Capital
Markets Jr Support],",")+2,1) & Left([Capital Markets Jr Support],1)))))

I have the name "Commercial Real Estate" selected for [Capital Markets
Referrel], "Smith, John" selected for [Capital Markets Referral] and "Doe,
John" selected for [Capital Markets Jr Support].

So my output on the report s/b Commercial Real Estate/JS/JD but only
Commercial Real Estate is only outputting to the report.

Should my code be different?

Thanks in advance.

 

[John Spencer]

I think you have your closing parentheses in the wrong place. I think you
want to calculate the Capital Markets Referral value and then append the
Secondary and Jr support in separate IIF statements.

IIF([Capital Markets Referral] ...)

& IIf([Capital Markets Referral Secondary] Is Null,"","/" &
Mid([Capital Markets Referral Secondary],
InStr(1,[Capital Markets Referral Secondary],",")+2,1) &
Left([Capital Markets Referral Secondary],1))

& IIf([Capital Markets Jr Support] Is Null,"",
"/" & Mid([Capital Markets Jr Support],
InStr(1,[Capital Markets Jr Support],",")+2,1)
& Left([Capital Markets Jr Support],1))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Daryl S]

PJ -

Your concatenation would only work for Sub Debt based on the parentheses.
Also, if the Capital Markets Referral is always the same as the contents of
the field except when null, use the nz to assign the null value, and the rest
will work. Try this:

=nz([Capital Markets Referral],"") & Mid([Capital Markets
Referral],InStr(1,[Capital Markets
Referral],",")+2,1) & Left([Capital Markets Referral],1)) & IIf([Capital
Markets Referral Secondary] Is Null,"","/" & Mid([Capital Markets Referral
Secondary],InStr(1,[Capital Markets Referral Secondary],",")+2,1) &
Left([Capital Markets Referral Secondary],1)) & IIf([Capital Markets Jr
Support] Is Null,"","/" & Mid([Capital Markets Jr Support],InStr(1,[Capital
Markets Jr Support],",")+2,1) & Left([Capital Markets Jr Support],1))

 

[KARL DEWEY]

I have the name "Commercial Real Estate" selected for [Capital Markets
Referrel],
Your nested IIF test true here --Agency",IIf([Capital Markets
Referral]="Commercial Real Estate","Commercial Real Estate",
--- and goes no further.