criteria for 2 date fields from 2 tables

[diana2ke]

Hello,

I'm having this problem for a long time, i don't know how to solve it...
please help a poor one!
So, I have one supplier, I'm receiving invoices from him, I make payments to
him and I need to know my debt to him for ex. at 30.June.2005. I made 2
tables Invoice (InvoiceID, InvoiceDate.......) and Payment (PaymentID,
PaymentsDate.....)
I made a query Debt with fields:
Invoice ID
InvoiceDate
InvoiceValue
PayDate (Last)
Pay Value (Sum)
Debt: [InvoiceValue]-Nz(Sum([PayValue]),0)

For Invoice Date wrote as Criteria : Between [forms]![filters]![Date1] And
[forms]![filters]![Date2]

The result is exactely what I need IF I want the debt today(set the whole
period), but if I want to know the debt between a previous period or at a
date previous... it doesn't work if I put same criteria for PayDate.
How should I do this? to get the Invoices between 2 dates AND payments
untill the end of chosen period?

Thanks a lot

 

[[MVP] S.Clark]

Does the Payment table have the InvoiceID, and is it linked in the query.

Other than that, your question is too confusing to answer. Please re-ask in
a more direct question.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[diana2ke]

Thanks for trying to help me and sorry for being confusing. Yes, Payment
table has InvoiceID and it is linked in the query.

Does the Payment table have the InvoiceID, and is it linked in the query.

Other than that, your question is too confusing to answer. Please re-ask in
a more direct question.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

Hello,

I'm having this problem for a long time, i don't know how to solve it...
please help a poor one!
So, I have one supplier, I'm receiving invoices from him, I make payments
to
him and I need to know my debt to him for ex. at 30.June.2005. I made 2
tables Invoice (InvoiceID, InvoiceDate.......) and Payment (PaymentID,
PaymentsDate.....)
I made a query Debt with fields:
Invoice ID
InvoiceDate
InvoiceValue
PayDate (Last)
Pay Value (Sum)
Debt: [InvoiceValue]-Nz(Sum([PayValue]),0)

For Invoice Date wrote as Criteria : Between [forms]![filters]![Date1] And
[forms]![filters]![Date2]

The result is exactely what I need IF I want the debt today(set the whole
period), but if I want to know the debt between a previous period or at a
date previous... it doesn't work if I put same criteria for PayDate.
How should I do this? to get the Invoices between 2 dates AND payments
untill the end of chosen period?

Thanks a lot