Null value for query criteria.

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

I am working with a data table storing information from
9811 invoices that have been paid on 142 accounts.

I need to create a query that will allow me to find by
account number those invoices that have not been paid for
the current year. Try as I might, I have only been able
to get this to work if I break it down by months.

I was hoping that I can create a query based on year to
date or by creating a parameter query and the "between"
dates criteria.

Dose anyone know what I am doing wrong or have an idea on
how to do this?

Thank you

Terry
 
Dear Terry:

The initial question makes me guess that your accounting is "open
item" - a type in which each payment is assigned to one or more
invoices, with a specific amount from the payment entered. In this
case, each invoice has a specific balance, and you can filter that for
a balance of 0 (or less) to see which are paid.

Are you thus able to determine the balance of each invoice in the
system?

If your accounting is not "open item" then it is almost certainly
"balance forward." In a balance forward system, Invoices do not have
a balance, only accounts have a balance. If an account is unpaid,
there is no sense in the question, "Which invoices are paid and which
are not." Since this is the most common method of keeping accounts,
because of the simplicity of not recording how each payment is
assigned to the invoices (or more correctly, how each credit is
assigned to each debit) it is not unlikely you are caught up in this
conundrum.

This said, it is possible in a balance forward system to apply all
payments received against all debits in date order, and then say
"these debits are unpaid" by simply choosing the most recent debits
until you have sufficient to add up to the total deficiency. However,
to do so is not strictly correct in accounting, but it is at least
sufficiently well defined to permit a query to be written that does
so.

If nothing else, I hope this at least tells you I don't know enought
specifics of your situation to offer any help at this time. Perhaps
you could give an example, using reasonable data in the actual tables
you have, and explain what the result should be, and what rules are
used to obtain that result.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top