Filter query by a date based on two separate fields.

[Marsha McCarthy]

Hi,

I have have an access database for my accounts receivable. I have a table named "Invoice Table" with the Invoice Data some of the key fields are Invoice Reference Number (auto number from access), Customer Name, Invoice Number, Invoice Date and Invoice Amount. I have another table named "Invoice Payment Table" which is linked to the Invoice Table by the Invoice Reference Number. This table has the following key fields, Payment Amount, Payment Date. Each invoice has part payments.

The tables and queries works fine when I pull the data that I want. However, I want to pull the customer balances as at a particular date if i put in the criteria under invoice date <12/31/2005, it pulls all the records less than that date. However, if a customer makes a payment in 2006 those records are also shown. If I place the came criteria under the Invoice Date field and the Payment Date field nothing is pulled. If I place the criteria under payment date field only, only the records with the payments are pulled.

Help needed.

[orangeCat]

How do you determine Customer Balance?
eg.
For a given Customer

Balance = Sum(All Invoiced Amounts) - Sum(All Payment Amounts) before ParticularDate

[Marsha McCarthy]

That is what i need help with...If i dont use the date criteria it calculates the customer balance accurately by subtracting the total payment amount from the invoice amount. However, if i want to know what is the balance at a particular date...that's where the trouble lies. I posted a new question to get help on that...maybe you could help me and I could email you the database i have done thus far.