How do I set up a nested statement?

[Guest]

I am having trouble getting a nested IIf statement to work correctly. Here
is what I have....

REP:
IIf([Invoices_With_Codes]![WHSE]="AZ",753,IIf([Invoices_With_Codes]![WHSE]="TX",404,[REPLIST]![REGN_CODE]))

In the table REPLIST the REGN_CODE field is a text field because it contains
alpha and numeric characters. The proble with using this statement in the
query is that it is changing the field to numeric so therefore not including
the alpha rows that I know should be in this query. How do I write this
statement to include both alpha and numeric and to leave it as a text field?

Thanks
Amy

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this:

IIf([Invoices_With_Codes]![WHSE]="AZ",753,
IIf([Invoices_With_Codes]![WHSE]="TX",404,
CInt([REPLIST]![REGN_CODE])))

Just convert the Regn_Code to an Integer "CInt(string_value)," or
whatever data type you want. See the Conversion Functions help article
for more info.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd166oechKqOuFEgEQKcXwCeNbPvquxZPNr3M5KRBeyMPS+5tFgAoP9L
uEok0gMUOxKR703rXqjySsn5
=pfr3
-----END PGP SIGNATURE-----

 

[John Spencer (MVP)]

Pardon me, but I think the OP wanted to return string data NOT Numeric data.
Otherwise, your solution looks excellent to me.

IIf([Invoices_With_Codes]![WHSE]="AZ","753",
IIf([Invoices_With_Codes]![WHSE]="TX","404",
[REPLIST]![REGN_CODE]))

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this:

IIf([Invoices_With_Codes]![WHSE]="AZ",753,
IIf([Invoices_With_Codes]![WHSE]="TX",404,
CInt([REPLIST]![REGN_CODE])))

Just convert the Regn_Code to an Integer "CInt(string_value)," or
whatever data type you want. See the Conversion Functions help article
for more info.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd166oechKqOuFEgEQKcXwCeNbPvquxZPNr3M5KRBeyMPS+5tFgAoP9L
uEok0gMUOxKR703rXqjySsn5
=pfr3
-----END PGP SIGNATURE-----

I am having trouble getting a nested IIf statement to work correctly. Here
is what I have....

REP:
IIf([Invoices_With_Codes]![WHSE]="AZ",753,IIf([Invoices_With_Codes]![WHSE]="TX",404,[REPLIST]![REGN_CODE]))

In the table REPLIST the REGN_CODE field is a text field because it contains
alpha and numeric characters. The proble with using this statement in the
query is that it is changing the field to numeric so therefore not including
the alpha rows that I know should be in this query. How do I write this
statement to include both alpha and numeric and to leave it as a text field?