Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2013
    Posts
    53

    Unanswered: Display Is Not Null criteria in calculated field

    Hey All, quick question here. How would I revise the following syntax so that the calculated object: INV Variance only displays values which are not Null? The following works but does not omit the Null records. Thanks in advance for your assistance!

    Code:
    SELECT PSPDAT_PSPMPBT.BTCHMV AS [Batch #], PSPDAT_PSPMPBT.PARTMV AS [Part #], PSPDAT_PSPMPBT.DESCMV AS Description, PSPDAT_PSPMPBT.PTOTMV AS [# of Parts], PSPDAT_PSPMPBT.CRTSMV AS [Batch Create Date], PSPDAT_PSPMPBT.UPTSMV AS [Batch Print Date], PSPDAT_PSPMPBT.EC01MV AS [Current Location], PSPDAT_PSPMPBT.SL01MV AS [New Location], PSPDAT_PSPPDCI.OHNQIM AS [Francis QTY], PSPDAT_PSPMPQS.MQTYMQ AS [Rochester QTY], IIf([PSPDAT_PSPPDCI].[OHNQIM]=[PSPDAT_PSPMPQS].[MQTYMQ],"","No Match") AS [QTY Match], IIf([PSPDAT_PSPPDCI].[OHNQIM]-[PSPDAT_PSPMPQS].[MQTYMQ]=0,"",[PSPDAT_PSPPDCI].[OHNQIM]-[PSPDAT_PSPMPQS].[MQTYMQ]) AS [INV Variance]
    FROM PSPDAT_PSPMPQS INNER JOIN (PSPDAT_PSPMPBT INNER JOIN PSPDAT_PSPPDCI ON (PSPDAT_PSPMPBT.PARTMV = PSPDAT_PSPPDCI.PARTIM) AND (PSPDAT_PSPMPBT.PDCCMV = PSPDAT_PSPPDCI.PDCCIM) AND (PSPDAT_PSPMPBT.CMPYMV = PSPDAT_PSPPDCI.CMPYIM)) ON (PSPDAT_PSPPDCI.PARTIM = PSPDAT_PSPMPQS.PARTMQ) AND (PSPDAT_PSPPDCI.PDCCIM = PSPDAT_PSPMPQS.PDCCMQ) AND (PSPDAT_PSPPDCI.CMPYIM = PSPDAT_PSPMPQS.CMPYMQ) AND (PSPDAT_PSPMPBT.PARTMV = PSPDAT_PSPMPQS.PARTMQ) AND (PSPDAT_PSPMPBT.PDCCMV = PSPDAT_PSPMPQS.PDCCMQ) AND (PSPDAT_PSPMPQS.CMPYMQ = PSPDAT_PSPMPBT.CMPYMV)
    WHERE (((IIf([PSPDAT_PSPPDCI].[OHNQIM]-[PSPDAT_PSPMPQS].[MQTYMQ]=0,"",[PSPDAT_PSPPDCI].[OHNQIM]-[PSPDAT_PSPMPQS].[MQTYMQ])) Is Not Null))
    GROUP BY PSPDAT_PSPMPBT.BTCHMV, PSPDAT_PSPMPBT.PARTMV, PSPDAT_PSPMPBT.DESCMV, PSPDAT_PSPMPBT.PTOTMV, PSPDAT_PSPMPBT.CRTSMV, PSPDAT_PSPMPBT.UPTSMV, PSPDAT_PSPMPBT.EC01MV, PSPDAT_PSPMPBT.SL01MV, PSPDAT_PSPPDCI.OHNQIM, PSPDAT_PSPMPQS.MQTYMQ, PSPDAT_PSPMPBT.PDCCMV
    HAVING (((PSPDAT_PSPMPBT.PDCCMV)="L01"));

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    There will be no Null values in that calculated field. You IIf function there is either returning a numeric value or "", which is a zero-length string. A ZLS is most emphatically NOT Null.
    In your WHERE clause, replace {IS NOT NULL} with {<> ""}. You could also strip out the parentheses in the WHERE clause not related to the IIf function, and it would still work.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jan 2013
    Posts
    53
    Thanks weejas, greatly appreciate it!

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Happy to help!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •