Query Null

Z

zyus

This is my select query script

SELECT [Tbl-SKS].IDNO, [Tbl-SKS].BRNCD, [Tbl-SKS].MODCD, [Tbl-SKS].CRLINE,
[Tbl-SKS].FCCD, [Tbl-SKS].PRLINE, [Tbl-SKS].PRTYP, [Tbl-SKS].ACNO,
[Tbl-SKS].LNAME, [Tbl-SKS].LNAMT, [Tbl-SKS].GRSBAL, [Tbl-SKS].NET_BAL,
[Tbl-SKS].COLMTH, [Tbl-SKS].ARRAMT, [Tbl-SKS].ARRMTH, [Tbl-SKS].NPLFLG,
[Tbl-SKS].NETIIS, [Tbl-SKS].NETPRO, [Tbl-SKS].CATCD, [Tbl-SKS].CATDESC,
[Tbl-SKS].LSTCOLDT, [Tbl-SKS].FSTRLSEDT, [Tbl-SKS].APRDT,
[Tbl-SKS].MARKOFFICER, [Tbl-SKS].UNDRAWN, [Tbl-PRODUCT_CATEGORY].RANK,
[Tbl-PRODUCT_CATEGORY].SUBCATDESC, [Tbl-SKS].MONTH, [Tbl-SKS].YR,
[Tbl-Branch].BRANCH, [Tbl-Branch].BRNSTAT, [Tbl-Branch].ZONE,
Tdeveloper.DEVELOPER, Tdeveloper.DEV_CODE, IIf([developer] Is Null,"ZZZ-Not
Specified",[developer]) AS DEVELOPERNAME
FROM (([Tbl-SKS] INNER JOIN [Tbl-PRODUCT_CATEGORY] ON ([Tbl-SKS].CRLINE =
[Tbl-PRODUCT_CATEGORY].CRLINE) AND ([Tbl-SKS].FCCD =
[Tbl-PRODUCT_CATEGORY].FCCD) AND ([Tbl-SKS].PRLINE =
[Tbl-PRODUCT_CATEGORY].PRLINE) AND ([Tbl-SKS].PRTYP =
[Tbl-PRODUCT_CATEGORY].PRTYP)) INNER JOIN [Tbl-Branch] ON [Tbl-SKS].BRNCD =
[Tbl-Branch].BRNCD) LEFT JOIN Tdeveloper ON [Tbl-SKS].ACNO = Tdeveloper.ACNO
WHERE ((([Tbl-SKS].CATCD)="HL"));

My problem is with my iff function where "IIf([developer] Is Null,"ZZZ-Not
Specified",[developer]) AS DEVELOPERNAME"

Not all null value in developer is returned as "ZZZ-Not Specified" , it
still remain null in developername field.

Thanks
 
J

John W. Vinson

My problem is with my iff function where "IIf([developer] Is Null,"ZZZ-Not
Specified",[developer]) AS DEVELOPERNAME"
Click to expand...

A much simpler expression would be

NZ([developer], "ZZZ-Not Specified")

.... IF the field is in fact NULL.
Not all null value in developer is returned as "ZZZ-Not Specified" , it
still remain null in developername field.
Click to expand...

Check the properties of the developer field in the table definition. Is "Allow
Zero Length String" set to Yes? If so, some records might contain a text
string of zero length (called for short a "ZLS"). This LOOKS just like a NULL,
but it's not NULL. NULL means "no value assigned, uninitialized, unknown" -
but a text string "" is a known, assigned, definite value (a String value of
zero bytes length).

If that's the case, either run an UPDATE query

UPDATE mytable SET developer = NULL WHERE developer = "";

or use

IIF(Len([Developer] & "") = 0, "ZZZ-Not Specified", [Developer])

The concatenation will return a ZLS if Developer is either NULL or a ZLS, and
Len() will see if it is in fact a ZLS.
 
Top