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


J

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
 
Ad

Advertisements

K

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]
 
J

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
 
J

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.
 
J

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
 
Ad

Advertisements

J

JohnW

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


John Spencer said:
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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top