Union Query Help - Return TRUE

[BerkshireGuy]

I have the following union query that works great, but I want to alter
the query to only return True (-1) for the TEST column.

Currently the test column DOES return 0 or -1. I just want the -1.

How can I place logic in this without creating another query.

Here is the union:

SELECT AgencyCode as ContactTypeID, AgencyShortName as ContactName,
Right(RegionNam e,1) as Region, "Agency" as ContactType,
ChkAgyDIS("Agys ",IIf([Forms]![frmMonthlyRepor ts]![txtReportType]="Agys",[contactTypeID]))
as TEST FROM dbo_V_EP_AllAge nciesForAppActi vity UNION ALL
SELECT DISTINCT DISCODE as ContactTypeID, LName & ", " & FName as
ContactName, Right(RegionNam e,1) as Region, "DIS" as ContactType,
ChkAgyDIS("Agys ",IIf([Forms]![frmMonthlyRepor ts]![txtReportType]="Agys",[contactTypeID]))
as TEST FROM qryDISContactsW ithRegionInfo UNION ALL SELECT DISTINCT
EnterpriseParty RoleDescription as ContactTypeID, LName & ", " & FName
as ContactName, IIF(LNAME=Perso n 1,"1",IIF(Instr (LNAME,'Person
2')>0,"2",IIF(I nstr(LNAME,'Per son 3')>0,"3",IIF(I nstr(LNAME,'Per son
4')>0,"4","UNKN OWN")))) as Region, "Region" as ContactType,
ChkAgyDIS("Agys ",IIf([Forms]![frmMonthlyRepor ts]![txtReportType]="Agys",[contactTypeID]))
as TEST FROM dbo_V_EP_GAEmai lContactsForApp Activity WHERE
EnterpriseParty RoleValue ='RSM';

TIA
-Brian

[pietlinden]

BerkshireGuy wrote:

I have the following union query that works great, but I want to alter
the query to only return True (-1) for the TEST column.

Currently the test column DOES return 0 or -1. I just want the -1.

How can I place logic in this without creating another query.

Here is the union:

SELECT AgencyCode as ContactTypeID, AgencyShortName as ContactName,
Right(RegionNam e,1) as Region, "Agency" as ContactType,
ChkAgyDIS("Agys ",IIf([Forms]![frmMonthlyRepor ts]![txtReportType]="Agys",[contactTypeID]))
as TEST FROM dbo_V_EP_AllAge nciesForAppActi vity UNION ALL
SELECT DISTINCT DISCODE as ContactTypeID, LName & ", " & FName as
ContactName, Right(RegionNam e,1) as Region, "DIS" as ContactType,
ChkAgyDIS("Agys ",IIf([Forms]![frmMonthlyRepor ts]![txtReportType]="Agys",[contactTypeID]))
as TEST FROM qryDISContactsW ithRegionInfo UNION ALL SELECT DISTINCT
EnterpriseParty RoleDescription as ContactTypeID, LName & ", " & FName
as ContactName, IIF(LNAME=Perso n 1,"1",IIF(Instr (LNAME,'Person
2')>0,"2",IIF(I nstr(LNAME,'Per son 3')>0,"3",IIF(I nstr(LNAME,'Per son
4')>0,"4","UNKN OWN")))) as Region, "Region" as ContactType,
ChkAgyDIS("Agys ",IIf([Forms]![frmMonthlyRepor ts]![txtReportType]="Agys",[contactTypeID]))
as TEST FROM dbo_V_EP_GAEmai lContactsForApp Activity WHERE
EnterpriseParty RoleValue ='RSM';

Where's the TEST=0 in your filter? If the value is called different
names in different tables, you have to alias them so they all have the
same name.

[Bob Quintal]

"BerkshireG uy" <bd*****@yahoo. comwrote in
news:11******** *************@d 34g2000cwd.goog legroups.com:

I have the following union query that works great, but I want
to alter the query to only return True (-1) for the TEST
column.

Currently the test column DOES return 0 or -1. I just want
the -1.

How can I place logic in this without creating another query.

Firstly you need to properly format your query, it's a dog's
breakfast. Break after keywords, after each field definition and
elsewhere as required.
SELECT
AgencyCode as ContactTypeID,
AgencyShortName as ContactName,
Right(RegionNam e,1) as Region,
"Agency" as ContactType,
ChkAgyDIS("Agys ",
IIf([Forms]![frmMonthlyRepor ts]![txtReportType]="Agys",
[contactTypeID])) as TEST
FROM
dbo_V_EP_AllAge nciesForAppActi vity

UNION ALL
SELECT DISTINCT
DISCODE as ContactTypeID,
LName & ", " & FName as ContactName,
Right(RegionNam e,1) as Region,
"DIS" as ContactType,
ChkAgyDIS("Agys ",
IIf([Forms]![frmMonthlyRepor ts]![txtReportType]="Agys",
[contactTypeID])) as TEST
FROM
qryDISContactsW ithRegionInfo

UNION ALL
SELECT DISTINCT
EnterpriseParty RoleDescription as ContactTypeID,
LName & ", " & FName
as ContactName,
IIF(LNAME=Perso n 1,"1",
IIF(Instr(LNAME ,'Person 2')>0,"2",
IIF(Instr(LNAME ,'Person 3')>0,"3",
IIF(Instr(LNAME ,'Person4')>0," 4",
"UNKNOWN")) )) as Region,
"Region" as ContactType,
ChkAgyDIS("Agys ",
IIf([Forms]![frmMonthlyRepor ts]![txtReportType]="Agys",
[contactTypeID])) as TEST
FROM
dbo_V_EP_GAEmai lContactsForApp Activity
WHERE
EnterpriseParty RoleValue ='RSM';

Then you need to insert a "WHERE clause individually in each of
the three queries, that limits the data to the true case of the
expression "test", after the "FROM" keyword and expression
FROM
dbo_V_EP_AllAge nciesForAppActi vity..
WHERE
IIf([Forms]![frmMonthlyRepor ts]![txtReportType]="Agys",
[contactTypeID])) = -1

is the first

the third already has a WHERE clause so you need to put an AND
to join the conditions
WHERE
EnterpriseParty RoleValue ='RSM';
AND
IIf([Forms]![frmMonthlyRepor ts]![txtReportType]="Agys",
[contactTypeID])) = -1

Your homework is to do the second WHERE Clause..

You also are missing quotes around 'person 1' in
IIF(LNAME=Perso n 1,"1",


Bob Q

Here is the union:

SELECT AgencyCode as ContactTypeID, AgencyShortName as
ContactName, Right(RegionNam e,1) as Region, "Agency" as
ContactType,
ChkAgyDIS("Agys ",IIf([Forms]![frmMonthlyRepor ts]!

[txtReportType

]="Agys",[contactTypeID]))
as TEST FROM dbo_V_EP_AllAge nciesForAppActi vity UNION ALL
SELECT DISTINCT DISCODE as ContactTypeID, LName & ", " & FName
as ContactName, Right(RegionNam e,1) as Region, "DIS" as
ContactType,
ChkAgyDIS("Agys ",IIf([Forms]![frmMonthlyRepor ts]!

[txtReportType

]="Agys",[contactTypeID]))
as TEST FROM qryDISContactsW ithRegionInfo UNION ALL SELECT
DISTINCT
EnterpriseParty RoleDescription as ContactTypeID, LName & ", "
& FName as ContactName, IIF(LNAME=Perso n
1,"1",IIF(Instr (LNAME,'Person
2')>0,"2",IIF(I nstr(LNAME,'Per son
3')>0,"3",IIF(I nstr(LNAME,'Per son 4')>0,"4","UNKN OWN")))) as
Region, "Region" as ContactType,
ChkAgyDIS("Agys ",IIf([Forms]![frmMonthlyRepor ts]!

[txtReportType

]="Agys",[contactTypeID]))
as TEST FROM dbo_V_EP_GAEmai lContactsForApp Activity WHERE
EnterpriseParty RoleValue ='RSM';

TIA
-Brian

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com