string together IIf statements into one text box?


T

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!!
 
Ad

Advertisements

J

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
 
D

Duane Hookom

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

Advertisements

T

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..!

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


--
Duane Hookom
Microsoft Access MVP


tish said:
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!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top