Filter Form by date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form I need to filter by a PayDate field. I need to get the most
recent PayDate and then display only the records whose most recent PayDate is
more than 60 days ago (from current date).I have two tables, Clients and
Payments. One to Many relationship from Clients to Payments. I need this form
filtered so I can print off statements for all Clients who haven't made a
payment in the last 60 days.

Thank You
 
=?Utf-8?B?cm9iZXJ0bTYwMDYzNQ==?=
I have a form I need to filter by a PayDate field. I need to get
the most recent PayDate and then display only the records whose
most recent PayDate is more than 60 days ago (from current date).I
have two tables, Clients and Payments. One to Many relationship
from Clients to Payments. I need this form filtered so I can print
off statements for all Clients who haven't made a payment in the
last 60 days.

Thank You
Click to expand...

you'll need to use a sub query in the form's recordsource which has
the criteria for the Client ID set to:

IN (SELECT payments.ClientID FROM payments
GROUP BY payments.ClientID
HAVING (((DateDiff("d",Max([payments].[paydate]),Date()))>60));)

and use that as a filter in the docmd.openform statement
 
=?Utf-8?B?cm9iZXJ0bTYwMDYzNQ==?=
Iv'e never worked with sub queries and I'm not sure how to go
about implementing your solution..could you explain it for me or
point me somewhere I could learn about it?
Click to expand...

here's a good tutorial.
http://www.sqlteam.com/article/using-subqueries-to-select-records
as is this
http://as400bks.rochester.ibm.com/iseries/v5r2/ic2924/index.htm?
info/sqlp/rbafymst141.htm

HTH, if you need more assistance after reading those, please post
back.

Q
Bob Quintal said:
=?Utf-8?B?cm9iZXJ0bTYwMDYzNQ==?=
I have a form I need to filter by a PayDate field. I need to
get the most recent PayDate and then display only the records
whose most recent PayDate is more than 60 days ago (from
current date).I have two tables, Clients and Payments. One to
Many relationship from Clients to Payments. I need this form
filtered so I can print off statements for all Clients who
haven't made a payment in the last 60 days.

Thank You
Click to expand...

you'll need to use a sub query in the form's recordsource which
has the criteria for the Client ID set to:

IN (SELECT payments.ClientID FROM payments
GROUP BY payments.ClientID
HAVING (((DateDiff("d",Max([payments].[paydate]),Date()))>60));)

and use that as a filter in the docmd.openform statement
Click to expand...
Click to expand...
 
Iv'e never worked with sub queries and I'm not sure how to go about
implementing your solution..could you explain it for me or point me somewhere
I could learn about it?

Bob Quintal said:
=?Utf-8?B?cm9iZXJ0bTYwMDYzNQ==?=
I have a form I need to filter by a PayDate field. I need to get
the most recent PayDate and then display only the records whose
most recent PayDate is more than 60 days ago (from current date).I
have two tables, Clients and Payments. One to Many relationship
from Clients to Payments. I need this form filtered so I can print
off statements for all Clients who haven't made a payment in the
last 60 days.

Thank You
Click to expand...

you'll need to use a sub query in the form's recordsource which has
the criteria for the Client ID set to:

IN (SELECT payments.ClientID FROM payments
GROUP BY payments.ClientID
HAVING (((DateDiff("d",Max([payments].[paydate]),Date()))>60));)

and use that as a filter in the docmd.openform statement
Click to expand...
 
Back
Top