string together IIf statements into one text box?

[tish]

So, I have a report with 10 text boxes. Each text box has in its control
source this basic code:

=IIf(IsNull([AccountCodeNumber]),"","AcctCodeWritten")

Where "AccountCodeNumber" refers to a field name in the "Contract" table and
"AcctCodeWritten" will be the code converted into a word (for people reading
the rerpot not familiar with what the codes refer to.) For each record, the
IIf statement will now print on the report the corresponding word when there
is a dollar value for a particular code.

Is it possible to put all of these IIf statements into one text box? Here's
why. As it is now, all 10 text boxes are laid out in two rows, 5 columns.
When text box 1, 5 and 10 have values, their corresponding words are printed
in the first slot, 5th and then 10th spot, with big spaces in between. I'd
like them right next to each other when items in between two codes are null.

I have looked and seen combined IIf statements, but they don't quite apply
to what I want to do and I've gotten tons of error messages..! Please and
thank you!!

 

[Jeff Boyce]

I can't be sure from the description, but it sounds like you have a number
of values you need to look up.

Have you considered putting the cross-walk values into a table and joining
that table to your data in a query? That way, you don't have to work out
the logic on nested IIF() functions. (and more importantly, when you start
using a new code, you just add it to the table -- you don't have to revisit
every place you used the IIF() function and rebuild it!)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Duane Hookom]

Your table structure sounds un-normalized. However
=IIf(IsNull([AccountCodeNumber]),"","AcctCodeWritten") &
IIf(IsNull([AnotherCode]),""," AnotherCodeWritten") etc.

 

[tish]

Thank you!! Just what I needed.. And so simple. I thought I had tried using &
in my formula but I guess not..!?!

Thanks again..!

Your table structure sounds un-normalized. However
=IIf(IsNull([AccountCodeNumber]),"","AcctCodeWritten") &
IIf(IsNull([AnotherCode]),""," AnotherCodeWritten") etc.


--
Duane Hookom
Microsoft Access MVP

So, I have a report with 10 text boxes. Each text box has in its control
source this basic code:

=IIf(IsNull([AccountCodeNumber]),"","AcctCodeWritten")

Where "AccountCodeNumber" refers to a field name in the "Contract" table and
"AcctCodeWritten" will be the code converted into a word (for people reading
the rerpot not familiar with what the codes refer to.) For each record, the
IIf statement will now print on the report the corresponding word when there
is a dollar value for a particular code.

Is it possible to put all of these IIf statements into one text box? Here's
why. As it is now, all 10 text boxes are laid out in two rows, 5 columns.
When text box 1, 5 and 10 have values, their corresponding words are printed
in the first slot, 5th and then 10th spot, with big spaces in between. I'd
like them right next to each other when items in between two codes are null.

I have looked and seen combined IIf statements, but they don't quite apply
to what I want to do and I've gotten tons of error messages..! Please and
thank you!!