How do I use nested Functions?

[Guest]

I am having trouble with a nested IIf statement. This is what I have and it
works but I also have Reps that have alpha characters, this statement works
fine for the numeric but does not bring over the alpha characters. In the
table that I am getting this information the [REGN_CODE] the field is a text
field and I would like for it to stay a text field but with this statement it
changes over to numeric.

REP: IIf([Invoices Final Test]![WHSE]="AZ",753,IIf([Invoices Final
Test]![WHSE]="TX",404,[Invoices Final Test]![REGN_CODE]))

Is their another statement that I need to be using?

Thanks so much.
Amy

 

[Dirk Goldgar]

I am having trouble with a nested IIf statement. This is what I have
and it works but I also have Reps that have alpha characters, this
statement works fine for the numeric but does not bring over the
alpha characters. In the table that I am getting this information
the [REGN_CODE] the field is a text field and I would like for it to
stay a text field but with this statement it changes over to numeric.

REP: IIf([Invoices Final Test]![WHSE]="AZ",753,IIf([Invoices Final
Test]![WHSE]="TX",404,[Invoices Final Test]![REGN_CODE]))

Is their another statement that I need to be using?

Thanks so much.
Amy

I guess Access is just making a bad guess as to what the output data
type should be, because the first couple of output values you're feeding
it are numbers, not text. Try this:

IIf([Invoices Final Test]![WHSE]="AZ", "753",
IIf([Invoices Final Test]![WHSE]="TX", "404",
[Invoices Final Test]![REGN_CODE]))

 

[Guest]

Thanks so much that did it.

I am having trouble with a nested IIf statement. This is what I have
and it works but I also have Reps that have alpha characters, this
statement works fine for the numeric but does not bring over the
alpha characters. In the table that I am getting this information
the [REGN_CODE] the field is a text field and I would like for it to
stay a text field but with this statement it changes over to numeric.

REP: IIf([Invoices Final Test]![WHSE]="AZ",753,IIf([Invoices Final
Test]![WHSE]="TX",404,[Invoices Final Test]![REGN_CODE]))

Is their another statement that I need to be using?

Thanks so much.
Amy

I guess Access is just making a bad guess as to what the output data
type should be, because the first couple of output values you're feeding
it are numbers, not text. Try this:

IIf([Invoices Final Test]![WHSE]="AZ", "753",
IIf([Invoices Final Test]![WHSE]="TX", "404",
[Invoices Final Test]![REGN_CODE]))

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)