Exclude Zeros from query (1 Viewer)

hllary

Registered User.
Local time
Yesterday, 17:45
Joined
Sep 23, 2019
Messages
80
The query below returns a number of records that have a value of zero. How do I exclude those records?

Code:
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

I’m here to help
Staff member
Local time
Yesterday, 17:45
Joined
Oct 29, 2018
Messages
21,358
Hi. A value of zero in which field? The Diff column?
 

hllary

Registered User.
Local time
Yesterday, 17:45
Joined
Sep 23, 2019
Messages
80
Yes, in the DIff Column
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 20:45
Joined
May 21, 2018
Messages
8,463
do you have a string value of "isnull"
masterlist_tbl.additionalfig <> "isnull"
or do you mean
masterlist_tbl.additionalfig is not null
 

hllary

Registered User.
Local time
Yesterday, 17:45
Joined
Sep 23, 2019
Messages
80
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

You've got your good things, and you've got mine.
Local time
Yesterday, 20:45
Joined
May 21, 2018
Messages
8,463
A little easier to read
SQL:
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"
    )
  );
 

Users who are viewing this thread

Top Bottom