Criteria

[AN60]

I have a query something as follows;

Employee
Date1
TaxHeld
TaxPaid

My problem is that there are other fields used by the date1 which do not need to be in this query and when the query is run I get some blank Taxheld & TaxPaid's because the date1 field will show all dates and the Tax fields are quite rightly blank. I have tried to remove the two Tax fields when both are blank using >0 in the criteria of each but this will remove data where either one is blank. I only want to remove the blanks where both TaxHeld & TaxPaid are null or zero. I'm sure this one is simple but sometimes you (I) can't see the forrest for the trees.

 

[o1110010]

.. I only want to remove the blanks where both TaxHeld & TaxPaid are null or zero.

Timber! Use the criteria "Is Not Null And >0" for it to work.

 

[AN60]

o1110010
Thank you for your suggestion, however, I still get the same result when I add Is not Null. I need to only get rid of entries where both Taxheld & taxpaid are zero. What I have so far is shown below.

WHERE ((([Job Record].DateDetails) Between [Forms]![Employee Tax]![txtFrom] And [Forms]![Employee Tax]![txtTo]) AND (([Job Record].TaxHeld) Is Not Null And ([Job Record].TaxHeld)>0) AND (([Job Record].TaxPaid) Is Not Null And ([Job Record].TaxPaid)>0))

 

[o1110010]

o1110010
Thank you for your suggestion, however, I still get the same result when I add Is not Null. I need to only get rid of entries where both Taxheld & taxpaid are zero. What I have so far is shown below.

WHERE ((([Job Record].DateDetails) Between [Forms]![Employee Tax]![txtFrom] And [Forms]![Employee Tax]![txtTo]) AND (([Job Record].TaxHeld) Is Not Null And ([Job Record].TaxHeld)>0) AND (([Job Record].TaxPaid) Is Not Null And ([Job Record].TaxPaid)>0))

Hm. Change the ANDs to ORs after Is Not Null. If that doesn't work.... *thinks*

 

[o1110010]

... In this case though the Nz() will simplify the condition.

Ah that slippery Nz(). I've only used it once so far. Good job Pat.

 

[AN60]

I've tried Pat's & o1110010's suggestions and neither works as desired. Pat's removes everything & o1110010's allows too much. I guess I could leave the blank/zero's there because they don't really do any harm but it would be a lot neater without them.

Does the date criteria over ride the other two, i.e. Between [Forms]![Employee Tax]![txtFrom] And [Forms]![Employee Tax]![txtTo]

 

[R6Flyer]

AN60,

Add an extra calculated field to your query along the lines of:

TaxTotal:Nz([Job Record].TaxHeld, 0)+Nz([Job Record].TaxPaid, 0)

In the criteria for this field set it to >0

 

[AN60]

R6Flyer
A BIG thank you. It now works 100% as desired.
To everyone else thank you also.

 

[AN60]

Pat
What can I say? Well done, your last one works too. Even though I am using R6Flyer's suggestion I will tuck yours away for future reference. Thank you very much.