If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query Null
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 |
#2
|
|||
|
|||
Query Null
On Wed, 26 Aug 2009 22:16:01 -0700, zyus
wrote: My problem is with my iff function where "IIf([developer] Is Null,"ZZZ-Not Specified",[developer]) AS DEVELOPERNAME" 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. 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|