Filtering Dates

  • Thread starter Thread starter Jeff P.
  • Start date Start date
J

Jeff P.

In my query I have several rows of date. Two of these rows display Due dates.
The first is a customer Original Due Date. The second is a Revised Due Date.
These are typically the same. On occasion the customer will change the date
which will adjust the Revised date. My queries typically have 5000 plus
lines. Finding a Revised Date change is like finding needle in a haystack;
but it is essential for of my job. I would like to filter on dates that do
not match showing a change from the original due date, so as to not have to
search for these discrepancies line for line.
 
Jeff

It all starts with the data.

I don't have a very clear picture of your underlying data structure...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
In my query I have several rows of date. Two of these rows display Due dates.
The first is a customer Original Due Date. The second is a Revised Due Date.
These are typically the same. On occasion the customer will change the date
which will adjust the Revised date. My queries typically have 5000 plus
lines. Finding a Revised Date change is like finding needle in a haystack;
but it is essential for of my job. I would like to filter on dates that do
not match showing a change from the original due date, so as to not have to
search for these discrepancies line for line.
Click to expand...

A criterion of [Revised Due Date] of

<> [Original Due Date]

will return all rows where these date values are unequal. Does that meet your
needs?

John W. Vinson [MVP]
 
Taking a WILD GUESS that your table structure has a CustomerID, a datefield
and a DateType which specifies the type of the date.

Query A
SELECT A.CustomerID, A.DateField
FROM TheTable as A
WHERE A.DateType = "Original Due Date"

Query B
SELECT B.CustomerID, A.DateField
FROM TheTable as B
WHERE B.DateType = "Revised Due Date"

Final query using Query A and B
SELECT A.CustomerID
, A.DateField as Original
, B.DateField as Revised
FROM A INNER JOIN B
ON A.CustomerId = B.CustomerID
WHERE A.DateField <> B.DateField

If you need an updatable query then you need to add one more step
SELECT TheTable.*
FROM TheTable
WHERE TheTable.CustomerID IN
(
SELECT A.CustomerID
FROM A INNER JOIN B
ON A.CustomerId = B.CustomerID
WHERE A.DateField <> B.DateField
)
AND TheTable.DateType in ("Original Due Date","Revised Due Date")

IF your tablenames and field names consist of only letters, numbers, and the
underscore character, then this could all be done in one query.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top