Multiple query

[Tuili63]

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.

 

[Howlsta]

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

 

[Tuili63]

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

 

[Fizzio]

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?

 

[Howlsta]

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

 

[Tuili63]

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.

 

[Rich]

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?