criteria question

[aussie_user]

Hi,

I'm having trouble getting a query to return exactly the results I want and was wondering if anyone had any suggestions on what I could do.

I have a database for various projects. They are given various amounts of funding according to a schedule of payments set up at the beginning of the project. Sometimes the projects do not require all of the funding. If they have already recieved the money then they submit a refund. If they haven't received the money yet, I make journal entries to adjust the amount of the project down.

I am trying to produce a report that shows the refund and adjusted down amounts for the current year. I have dates attached to the refunds and adjusted down amounts. In my query if I set the criteria as

>#1/01/2002# for refunds then OR for adjusted down I get all entries for both fields even if they happened in a previous year. Eg. I have one project that had a refund in 2001, but the adjustment down was not made until 2002 for the remaining unpaid funds. I want a report that will agree with the reports we get from the Finance Department who only include details for the current year.

If I set the criteria as AND for the two date fields I will only get records who have a refund and adjusted down in 2002.

Is there a way I can use NOT to exclude the refund from 2001 but still retain the adjusted down figure for 2002?


Thanks for any suggestions.

 

[aussie_user]

I have a table called Grants. It includes the ProjectID which links the table to other information about the project. Fields in this table include GrantAmount, AmountRefunded, DateOfRefund, AmtAdjustedDown and DateAdjustedDown.