Forum Discussion

JohnRomano's avatar
JohnRomano
Qrew Cadet
3 months ago
Solved

Using AND with Null values

I have a formula with multiple Null and Not Null values, one of the nested statements needs to look at two fields so I used AND, but this generates an error "AND" cannot be applied. 

If(
IsNull([Pallet Grp "C"]),0,
((Nz([Low, Carton "C"] and (IsNull([high, carton "C"])),1)
[high, carton "C"]-[low, carton "C"]+1)

Can someone recommend a solution--if possible.

formulas
  • JohnRomano's avatar
    JohnRomano
    3 months ago

    I appreciate the help, I was trying to be as direct as possible--noted for the next time. Your solution produced the desired outcome. Thank you.

4 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 months ago

    Sure, try this

    If(
    IsNull([Pallet Grp "C"]),0,
    Nz([Low, Carton "C"]) and IsNull([high, carton "C"]),1,
    [high, carton "C"]-[low, carton "C"]+1)

    • JohnRomano's avatar
      JohnRomano
      Qrew Cadet
      3 months ago

      Hi Mark,

      I see that I had a few misplaced (), which I corrected; however, the AND statement is still not working. 

       

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 months ago

    My initial suggestion was incorrect, but maybe this is what you want.  But I think it would help if you said in plain english what you want the formula to do.

    Also when you are evaluating numeric fields to tell if they are null, it requires that field's properties to have the checkbox "treat blank as zero" deselected.

    If(
    IsNull([Pallet Grp "C"]),0,
    Nz([Low, Carton "C"])=0 and IsNull([high, carton "C"]),1,
    [high, carton "C"]-[low, carton "C"]+1)

    • JohnRomano's avatar
      JohnRomano
      Qrew Cadet
      3 months ago

      I appreciate the help, I was trying to be as direct as possible--noted for the next time. Your solution produced the desired outcome. Thank you.