Report Questions - Access 2000

[Gale Coleman]

Hello all,

I have a report that is based on a query.

I want to show all names and addresses for people who are not marked
protected.

I know I have to make an IIF statement and am working on something like
this:

I put the following in the address field of the report:

=IIf([ProtectedReason]="D","cannot list this")

I have a field called ProtectedReason that can have a code of either
a,b,c,d,e,i

Depending on the code that is in the field ProtectedReason, I want the
address to either show up or say something like "cannot list this,
protected".

So far when I write this statement, nothing comes up in the address field at
all. What can I do differently to either show the address if there is no
protected code or show something else if there is a protected code?

thanks

Gale

 

[Jim Bunton]

query:
SELECT this, that, IIF([ProtectedReason] ="D","Address Disabled", [Address])
AS MyAddressModification, othercols

if you don't have an 'address' column you could substitute [Street] & " " &
[Town] & " " & [Postcode]"

 

[Gale Coleman]

OK, here is my select query before putting the IIF statement in it.

SELECT CLIENTSW.CASENUM, CLIENTSW.CASETYPE, CLIENTSW.PCODE,
CLIENTSW.SPECPCODE, CLIENTSW.SNUM, CLIENTSW.CLNAME, CLIENTSW.CFNAME,
CLIENTSW.CMI, CLIENTSW.SSN, CLIENTSW.DOPEN, CLIENTSW.OFFICENUM,
CLIENTSW.FUNDSNUM, CLIENTSW.DCLOSED, CLIENTSW.RCLOSED, CLIENTSW.CADDRESS,
CLIENTSW.CCITY, CLIENTSW.CSTATE, CLIENTSW.CZIP, CLIENTSW.CACODE,
CLIENTSW.CPHONE, CLIENTSW.CCOUNTY, CLIENTSW.SEX, CLIENTSW.BDATE,
CLIENTSW.AGE, CLIENTSW.RACE, CLIENTSW.LANGUAGE, CLIENTSW.CHILDREN,
CLIENTSW.ADULTS, CLIENTSW.INCSOURCE, CLIENTSW.TINCOME, CLIENTSW.KNOW,
CLIENTSW.TINTAKE, CLIENTSW.PREVIOUS, CLIENTSW.CHOURS, CLIENTSW.SUMMARY,
CLIENTSW.TRANSFER, CLIENTSW.COCOUNSEL, CLIENTSW.SPCODE1, CLIENTSW.SPCODE2,
CLIENTSW.SPCODE3, CLIENTSW.CLINICNUM, CLIENTSW.PACKET, CLIENTSW.AFEESAMT,
CLIENTSW.EXPENSESAMT, CLIENTSW.MAJOR, CLIENTSW.IMPACT, CLIENTSW.CLASSACT,
CLIENTSW.PHELPED, CLIENTSW.RECOVERY, CLIENTSW.GROUPC, CLIENTSW.OUTCOME,
CLIENTSW.PRIORITY, CLIENTSW.UNDUPLICATED, CLIENTSW.LIVEAR,
CLIENTSW.MARITALS, CLIENTSW.SLNAME, CLIENTSW.SFNAME, CLIENTSW.CITIZEN,
CLIENTSW.RREJECT, CLIENTSW.MAINBENEFIT, CLIENTSW.HANDICAPPED,
CLIENTSW.SPROGRAM, CLIENTSW.RECOVERYM, CLIENTSW.AVOID, CLIENTSW.AVOIDM,
CLIENTSW.PhoneRef, CLIENTSW.GoodStory, CLIENTSW.CStatus, CLIENTSW.Ext,
CLIENTSW.MemoChange, CLIENTSW.DomViol, CLIENTSW.LSCElig, CLIENTSW.Sent,
CLIENTSW.DDestroy, LITIGATION.Casenum, LITIGATION.Protected,
LITIGATION.ProtectedReason, LITIGATION.ClientIs, LITIGATION.CauseofAction,
LITIGATION.Judge1, LITIGATION.Judge2, LITIGATION.Judge3,
LITIGATION.CourtName1, LITIGATION.CourtName2, LITIGATION.CourtName3,
LITIGATION.CourtNum1, LITIGATION.CourtNum2, LITIGATION.CourtNum3,
LITIGATION.CourtAdd1, LITIGATION.CourtAdd2, LITIGATION.CourtAdd3,
LITIGATION.CourtCity1, LITIGATION.CourtCity2, LITIGATION.CourtCity3,
LITIGATION.CourtZip1, LITIGATION.CourtZip2, LITIGATION.CourtZip3,
LITIGATION.CourtState1, LITIGATION.CourtState2, LITIGATION.CourtState3,
LITIGATION.DateB1, LITIGATION.DateE1, LITIGATION.DateB2, LITIGATION.DateE2,
LITIGATION.DateB3, LITIGATION.DateE3, LITIGATIONPARTY.LitPar,
LITIGATIONPARTY.Casenum, LITIGATIONPARTY.PartyFname,
LITIGATIONPARTY.PartyLname, LITIGATIONPARTY.PartyAddress,
LITIGATIONPARTY.PartyCity, LITIGATIONPARTY.PartyState,
LITIGATIONPARTY.PartyZip, LITIGATIONPARTY.PartyIs,
LITIGATIONPARTY.PartyComment, LITIGATIONPARTY.PartyAcode,
LITIGATIONPARTY.PartyPhone, subPCODE.PNAME
FROM ((CLIENTSW INNER JOIN LITIGATION ON CLIENTSW.CASENUM =
LITIGATION.Casenum) LEFT JOIN LITIGATIONPARTY ON CLIENTSW.CASENUM =
LITIGATIONPARTY.Casenum) INNER JOIN subPCODE ON CLIENTSW.PCODE =
subPCODE.PCODENUM
WHERE (((CLIENTSW.FUNDSNUM)<>21) AND ((LITIGATION.ClientIs)<>"D") AND
((LITIGATION.DateE1) Between [enter beginning date] And [enter ending date]
Or (LITIGATION.DateE1) Is Null));

Where would I put IIF([ProtectedReason] ="D","Address Disabled",
[CLIENTSW.CADDRESS]) AS MyAddressModification?

I get all kinds of different errors when I put it in on the end.

The fields that I don't want to show if there is a code in the
ProtectedReason box are ClientsW.CLname and
ClientsW.CFname,CLIENTSW.CADDRESS, CLIENTSW.CCITY, CLIENTSW.CSTATE,
CLIENTSW.CZIP and LITIGATIONPARTY.PartyFname, LITIGATIONPARTY.PartyLname,
LITIGATIONPARTY.PartyAddress, LITIGATIONPARTY.PartyCity,
LITIGATIONPARTY.PartyState, LITIGATIONPARTY.PartyZip. I want to show the
"Address cannot be shown" words.

Is there a different way to do it?

Gale

 

[Gale Coleman]

OK, I got it figured out,

Thanks,

Gale