IIF Functions

[zyus]

I have no problem to retrieve records in select query by these following
criteria

CATCD
"HL"

PRLINE
"COM" Or "GHOME" Or "BBAH"

PRTYP
"COM1" Or "COM2" Or "M10" Or "M9" Or "M9 2T" Or "COM-1"

My problem is to use IIF function in query to Tag those records that matched
the above criteria as "Shoplot" else "NA".

Thanks

 

[Klatuu]

So where does the NA go?
Either you return some records or you don't. Need some more explanation on
that.
But for your criteria, you don't need an Iif statement. This should do it:

WHERE [CATCD] = "HL" AND [PRLINE] IN ("COM", "GHOME", "BBAH") AND PRTYP IN
("COM1", "COM2", "M10", "M9", "M9 2T", "COM-1")

 

[BruceM]

You can create a calculated query field:

Tagged: IIf([CATCD] = "HL" Or ([PRLINE] = "COM" Or [PRLINE] = "GHOME" Or
[PRLINE] = "BBAH") Or ([PRTYP] = "COM1" Or [PRTYP} =
"COM10"),"Shoplot","N/A")

I left a few items out of the PRTYP listing. You can add them in. Also, I
assumed you wanted "Shoplot" if any of the three fields show any items you
have listed, but if [CATCD] = "HL" must be true in all cases you could use a
nested IIf:

Tagged: IIf([CATCD] = "HL",IIf(([PRLINE] = "COM" Or [PRLINE] = "GHOME" Or
[PRLINE] = "BBAH"),IIf(([PRTYP] = "COM1" Or [PRTYP} =
"COM10"),"Shoplot","N/A")))

 

[Klatuu]

Your solution will work, but it is harder to read and slower to execute.
--
Dave Hargis, Microsoft Access MVP

You can create a calculated query field:

Tagged: IIf([CATCD] = "HL" Or ([PRLINE] = "COM" Or [PRLINE] = "GHOME" Or
[PRLINE] = "BBAH") Or ([PRTYP] = "COM1" Or [PRTYP} =
"COM10"),"Shoplot","N/A")

I left a few items out of the PRTYP listing. You can add them in. Also, I
assumed you wanted "Shoplot" if any of the three fields show any items you
have listed, but if [CATCD] = "HL" must be true in all cases you could use a
nested IIf:

Tagged: IIf([CATCD] = "HL",IIf(([PRLINE] = "COM" Or [PRLINE] = "GHOME" Or
[PRLINE] = "BBAH"),IIf(([PRTYP] = "COM1" Or [PRTYP} =
"COM10"),"Shoplot","N/A")))

I have no problem to retrieve records in select query by these following
criteria

CATCD
"HL"

PRLINE
"COM" Or "GHOME" Or "BBAH"

PRTYP
"COM1" Or "COM2" Or "M10" Or "M9" Or "M9 2T" Or "COM-1"

My problem is to use IIF function in query to Tag those records that
matched
the above criteria as "Shoplot" else "NA".

Thanks

 

[BruceM]

I had understood the OP to mean "Shoplot" should appear somewhere (a
calcualted field) if the specified conditions are met; otherwise "N/A"
should appear. I understand (I think) that you are setting a WHERE
condition, but can that be used is such a way to mark a record as either
"Shoplot" or "N/A"?

My question here assumes I understood the OP correctly, which is not
necessarily the case.

Your solution will work, but it is harder to read and slower to execute.
--
Dave Hargis, Microsoft Access MVP

You can create a calculated query field:

Tagged: IIf([CATCD] = "HL" Or ([PRLINE] = "COM" Or [PRLINE] = "GHOME" Or
[PRLINE] = "BBAH") Or ([PRTYP] = "COM1" Or [PRTYP} =
"COM10"),"Shoplot","N/A")

I left a few items out of the PRTYP listing. You can add them in. Also,
I
assumed you wanted "Shoplot" if any of the three fields show any items
you
have listed, but if [CATCD] = "HL" must be true in all cases you could
use a
nested IIf:

Tagged: IIf([CATCD] = "HL",IIf(([PRLINE] = "COM" Or [PRLINE] = "GHOME" Or
[PRLINE] = "BBAH"),IIf(([PRTYP] = "COM1" Or [PRTYP} =
"COM10"),"Shoplot","N/A")))

I have no problem to retrieve records in select query by these following
criteria

CATCD
"HL"

PRLINE
"COM" Or "GHOME" Or "BBAH"

PRTYP
"COM1" Or "COM2" Or "M10" Or "M9" Or "M9 2T" Or "COM-1"

My problem is to use IIF function in query to Tag those records that
matched
the above criteria as "Shoplot" else "NA".

Thanks

 

[Klatuu]

I think you are correct, Bruce. I had trouble understanding the OP as well.


--
Dave Hargis, Microsoft Access MVP

I had understood the OP to mean "Shoplot" should appear somewhere (a
calcualted field) if the specified conditions are met; otherwise "N/A"
should appear. I understand (I think) that you are setting a WHERE
condition, but can that be used is such a way to mark a record as either
"Shoplot" or "N/A"?

My question here assumes I understood the OP correctly, which is not
necessarily the case.

Your solution will work, but it is harder to read and slower to execute.
--
Dave Hargis, Microsoft Access MVP

You can create a calculated query field:

Tagged: IIf([CATCD] = "HL" Or ([PRLINE] = "COM" Or [PRLINE] = "GHOME" Or
[PRLINE] = "BBAH") Or ([PRTYP] = "COM1" Or [PRTYP} =
"COM10"),"Shoplot","N/A")

I left a few items out of the PRTYP listing. You can add them in. Also,
I
assumed you wanted "Shoplot" if any of the three fields show any items
you
have listed, but if [CATCD] = "HL" must be true in all cases you could
use a
nested IIf:

Tagged: IIf([CATCD] = "HL",IIf(([PRLINE] = "COM" Or [PRLINE] = "GHOME" Or
[PRLINE] = "BBAH"),IIf(([PRTYP] = "COM1" Or [PRTYP} =
"COM10"),"Shoplot","N/A")))

I have no problem to retrieve records in select query by these following
criteria

CATCD
"HL"

PRLINE
"COM" Or "GHOME" Or "BBAH"

PRTYP
"COM1" Or "COM2" Or "M10" Or "M9" Or "M9 2T" Or "COM-1"

My problem is to use IIF function in query to Tag those records that
matched
the above criteria as "Shoplot" else "NA".

Thanks

 

[Hans Up]

I have no problem to retrieve records in select query by these following
criteria

CATCD
"HL"

PRLINE
"COM" Or "GHOME" Or "BBAH"

PRTYP
"COM1" Or "COM2" Or "M10" Or "M9" Or "M9 2T" Or "COM-1"

My problem is to use IIF function in query to Tag those records that matched
the above criteria as "Shoplot" else "NA".

So you can create a query with the criteria for the records which should
be flagged as "Shoplot". Use that one as a subquery, where your main
table is LEFT OUTER joined on its primary key to the subquery.

Here is an example.

SELECT
a.id
, a.some_text
, IIf(b.id Is Null,"NA","Shoplot") AS flag
FROM
tblFoo AS a
LEFT JOIN (
SELECT
id
FROM
tblFoo
WHERE
some_text In ("a", "c", "d")
) AS b ON a.id = b.id;

The result set, in CSV format, looks like this:

"id","some_text","flag"
1,"a","Shoplot"
2,"b","NA"
3,"c","Shoplot"
4,"d","Shoplot"
5,"e","NA"
6,"f","NA"
7,"g","NA"
8,"h","NA"