I need to pull the records that are between two dates I specify [Dates Received] or have a Balance. But I don't want to pull the records that have a balance but aren't between the two dates I specify.

Here are some of the fields

Name
Amount Received
Date Received
Balance.

I currently have
[Dates Received] Between # And #
OR [Balance] >0

I hope I have explained it well enough. Thank you for your help.

If you only want the records between that date then why don't you just omit the OR [Balance] bit.
"I don't want to pull the records that have a balance but aren't between the two dates I specify."
Surely the balance is irrelevant if what you said above is correct.

rich

Rich,

Unfortunately, the balance field is relevant. I do need those records to show as well.

Reason: The query pulls money received during specific dates. These monies are calculated out for commissions earned. I also need to show the unpaid balances on the other records in the database.

Tuili

Your initial statement contradicts itself.

First Bit 'I need to pull the records that are between two dates I specify [Dates Received] or have a Balance' =
(Between #date# And #Date) OR Balance >0

Second Bit 'I don't want to pull the records that have a balance but aren't between the two dates I specify' =
(Between #date# And #Date) AND Balance >0

So Essentially they HAVE to fall between the 2 dates AND have a balance? Is this correct?

Thanks Fizzio,

That's the point I was trying to make except not very well! The criteria does contradict itself.
Tuili63, maybe you could try and explain it again?

cheers

Rich

Sorry for all the confusion. Guess that's due to the haze after a 3 day weekend.

After rereading the posts I guess there is contradictions. Let me see if I can make myself clearer.

These are the results I need:

1. Records within the dates received criteria.

2. Records with balances. BUT; EXLUDE the records with balances if they have received dates outside the range specified.

The current criteria I have is a either or which isn't working because of the BUT I stated in #2 above.

I surely hope that clears things up. I again apologize for all the confusion.

Well I'm confused too, if your looking for monies recd. during a specified period then the query will not show balances outside of those dates anyway. If your looking for the balance not within those confines then you need a second query. But if they don't show in the first why do you need the second?