and-or

  • Thread starter Thread starter rob p
  • Start date Start date
R

rob p

This is an unbound text box:
=DSum("Amount","tblquery23",("[StateTaxCode]='WI' and ([EarningCode]<>Null
or [DeductionCode]<>Null)"))

I want total if StateTaxCode=WI and NOTHING is in both EarningCode and
DeductionCode fields. The above gives the opposite of what I want - which I
could subtract from the sum of all WI amounts.

If I change formula "([EarningCode]=Null and [DeductionCode]=Null)" I get
nothing.Yet there are records with nothing in both the EC and DC fields that
I want to add up. What am I missing?

thanks.
 
Null does not equal anything, ever...including Null. And I think you want
the "or" to be "and" for the last two checks.

Use this syntax instead:

=DSum("Amount","tblquery23",("[StateTaxCode]='WI' and ([EarningCode] Is Not
Null
And [DeductionCode] Is Not Null)"))
 
Back
Top