date poser

[Lapchien]

2 tables, 2 fields, both dates. both show dates of payments - 1 expected
date, the other actual. The 'actual' date is always a few days further on
than the 'expected' date. I need a query that compares the results of both
dates, and shows a message box if any differences are, say, more than 2
weeks apart..?

 

[Gerald Stanley]

Try something along the following lines

SELECT <yourtable1.yourdate1> , <yourtable2.yourdate2>
FROM <yourtable1> INNER JOIN <yourtable2> ON <yourrelationship>
WHERE DateDiff("d", <yourtable1.yourdate1>,
<yourtable2.yourdate2>) > 14

You will have to fill in your table and date names and the
relationship between the two tables. The above assumes
that you mean 14 days. You should refer to the Help on the
DateDiff function if you want to employ a different
definition of week (e.g the number of Mondays between two
dates)

Hope That Helps
Gerald Stanley MCSD

 

[John Vinson]

2 tables, 2 fields, both dates. both show dates of payments - 1 expected
date, the other actual. The 'actual' date is always a few days further on
than the 'expected' date. I need a query that compares the results of both
dates, and shows a message box if any differences are, say, more than 2
weeks apart..?

Put a calculated field in the Query:

DaysToPayment: DateDiff("d", [Expected], [Paid])

This will be an integer number of days; you can put a criterion on the
field of

14

to find all payments over two weeks old.

To catch those records that haven't been paid yet at all, use

NZ([Paid], Date())

instead of [Paid].