Find records in a field based on a date range - JCW

[JohnW]

I am looking to be able to run a report that shows who has not paid their
bill for the month. I have a paydate field that I want to search in and I
want to show the people who have NOT paid or the ones that do NOT have a
record in this paydate field between two dates. And I want to be prompted in
a query to input the first date and then the next. For example for September
the bills should all be paid by 9/8 so I would want the records for people
who do not have a value for paydate between 8/25/2009 to 9/8/2009 which
should cover when the bills are normally paid.

Thanks for your help

 

[KARL DEWEY]

Try these --
WHERE [paydate] Between [Enter start date] AND [Enter end date]
Or
In the query design grid --
FIELD row - [paydate]
CRITERIA row - Between [Enter start date] AND [Enter end date]

 

[John Spencer]

To find something that does not exist, it is usually easiest to find every
records where the value DOES exist. Then use that to identify the other
records in an unmatched query. The unmatched query wizard can build the
second step for you based on the query and the original table.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[JohnW]

John

Ok I have a "between" criteria in my Paydate field in a query that will show
me the payments made between two dates. I used
Between [Enter Start Date] And [Enter Finish Date]
That shows me the people who have paid during the inputed period of time.
Now how do I build the unmatched query??

Thanks for your help.

 

[John Spencer]

Open up a new query. Use the Unmatched query wizard

Add your table of people and the query of those who have paid.
Follow the prompts.

You should end up with a query that has an SQL statement that looks something like

SELECT Persons.*
FROM tblPersons LEFT JOIN QueryPaidAlready
ON tblPersons.PersonID = QueryPaidAlready.PersonID
WHERE QueryPaidAlready.PersonID is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[JohnW]

Once again thanks a lot. This looks like it will work just the way I needed
it.
--
JCW

Open up a new query. Use the Unmatched query wizard

Add your table of people and the query of those who have paid.
Follow the prompts.

You should end up with a query that has an SQL statement that looks something like

SELECT Persons.*
FROM tblPersons LEFT JOIN QueryPaidAlready
ON tblPersons.PersonID = QueryPaidAlready.PersonID
WHERE QueryPaidAlready.PersonID is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John

Ok I have a "between" criteria in my Paydate field in a query that will show
me the payments made between two dates. I used
Between [Enter Start Date] And [Enter Finish Date]
That shows me the people who have paid during the inputed period of time.
Now how do I build the unmatched query??

Thanks for your help.