Exclude Zeros from query

[hllary]

The query below returns a number of records that have a value of zero. How do I exclude those records?

SELECT Len([MasterList_tbl]![AdditionalFig])-Len(Replace([MasterList_tbl]![AdditionalFig],",","")) AS Fig, MasterList_tbl.MasterNum, Len([MasterList_tbl]![AdditionalItem])-Len(Replace([MasterList_tbl]![AdditionalItem],",","")) AS Item, MasterList_tbl.AdditionalFig, MasterList_tbl.AdditionalItem, Len([MasterList_tbl]![AdditionalFig])-Len(Replace([MasterList_tbl]![AdditionalFig],",",""))-(Len([MasterList_tbl]![AdditionalItem])-Len(Replace([MasterList_tbl]![AdditionalItem],",",""))) AS Diff
FROM MasterList_tbl
WHERE (((MasterList_tbl.AdditionalFig)<>"IsNull"));

 

[theDBguy]

Hi. A value of zero in which field? The Diff column?

 

[hllary]

Yes, in the DIff Column

 

[MajP]

do you have a string value of "isnull"
masterlist_tbl.additionalfig <> "isnull"
or do you mean
masterlist_tbl.additionalfig is not null

 

[hllary]

sorry, I've been playing with the code and I changed a part I should not have. It should be is not null or not "isnull". I'm trying to get the AS DIFF to show records that do not equal zero.

 

[MajP]

A little easier to read

SELECT
  Len(
    [MasterList_tbl] ! [AdditionalFig]
  )- Len(
    Replace(
      [MasterList_tbl] ! [AdditionalFig],
      ",", ""
    )
  ) AS Fig,
  MasterList_tbl.MasterNum,
  Len(
    [MasterList_tbl] ! [AdditionalItem]
  )- Len(
    Replace(
      [MasterList_tbl] ! [AdditionalItem],
      ",", ""
    )
  ) AS Item,
  MasterList_tbl.AdditionalFig,
  MasterList_tbl.AdditionalItem,
  Len(
    [MasterList_tbl] ! [AdditionalFig]
  )- Len(
    Replace(
      [MasterList_tbl] ! [AdditionalFig],
      ",", ""
    )
  )-(
    Len(
      [MasterList_tbl] ! [AdditionalItem]
    )- Len(
      Replace(
        [MasterList_tbl] ! [AdditionalItem],
        ",", ""
      )
    )
  ) AS Diff
FROM
  MasterList_tbl
WHERE
  (
    (
      (MasterList_tbl.AdditionalFig)<> "IsNull"
    )
  );