IIF Statement Help

  • Thread starter Thread starter Jeremy Dove
  • Start date Start date
J

Jeremy Dove

I am having a problem with an IIF statement.

In the field there are 3 different data types ( I , P, R)

I am trying to get the statement to show 2 as aliases and
leave the 3rd out.

Currently it will separate 2 but will make the 3rd the
second.

Here is the code:

SELECT IIf([APPS_PA_COMMITMENT_TXNS]![LINE_TYPE]
='R',"REQUISITIONS","PURCHASE ORDERS") AS TYPE,

Please let me know if you can help.

Thanks
 
Jeremy,
If you are trying to exclude rows that have an 'I' from
your query results, the IIF statement in the select won't
do it.
You want to exclude it in your where statement for sql
 
How would you word the code then because the code I
showed turns the "I"s into "P"s so you can't see the "I"s
any longer as "I"s
-----Original Message-----
Jeremy,
If you are trying to exclude rows that have an 'I' from
your query results, the IIF statement in the select won't
do it.
You want to exclude it in your where statement for sql
or have criteria of <>"I" for [LINE_TYPE]if you are using
query design grid.
-----Original Message-----
I am having a problem with an IIF statement.

In the field there are 3 different data types ( I , P, R)

I am trying to get the statement to show 2 as aliases and
leave the 3rd out.

Currently it will separate 2 but will make the 3rd the
second.

Here is the code:

SELECT IIf([APPS_PA_COMMITMENT_TXNS]![LINE_TYPE]
='R',"REQUISITIONS","PURCHASE ORDERS") AS TYPE,

Please let me know if you can help.

Thanks
.
Click to expand...
.
Click to expand...
 
Okay, I want to make sure I understand what you are
trying to do. Assume you have a table that you are
querying against. It has 6 rows of data, 2 with I's, 2
with P's and 2 with R's.
I think you want your select to return 4 rows, the ones
with P or R. You want the actual text,(Purchase Orders,
Requisitions) returned instead of just the letters.

If you are writing sql, it would be something like:

SELECT
field1,! any other stuff you are selecting
field2,
IIf([APPS_PA_COMMITMENT_TXNS]![LINE_TYPE]
'R',"REQUISITIONS","PURCHASE ORDERS") AS TYPE
FROM APPS_PA_COMMITMENT_TXNS
-----Original Message-----
How would you word the code then because the code I
showed turns the "I"s into "P"s so you can't see the "I"s
any longer as "I"s
-----Original Message-----
Jeremy,
If you are trying to exclude rows that have an 'I' from
your query results, the IIF statement in the select won't
do it.
You want to exclude it in your where statement for sql
or have criteria of <>"I" for [LINE_TYPE]if you are using
query design grid.
-----Original Message-----
I am having a problem with an IIF statement.

In the field there are 3 different data types ( I , P, R)

I am trying to get the statement to show 2 as aliases and
leave the 3rd out.

Currently it will separate 2 but will make the 3rd the
second.

Here is the code:

SELECT IIf([APPS_PA_COMMITMENT_TXNS]![LINE_TYPE]
='R',"REQUISITIONS","PURCHASE ORDERS") AS TYPE,

Please let me know if you can help.

Thanks
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top