date calculation (1 Viewer)

[cvaccess]

I am trying to subtract the date in one field to get another date in another field. For example, the date in ADJ_DATE field is 20020718, I want the SCRUB_DATE field to be ADJ_DATE-1, 20020717. Why doesn't this query work?

SELECT pend_summary.RPT_DATE, pend_summary.ADJ_DATE, pend_summary.SCRUB_DATE
FROM pend_summary
WHERE ((pend_summary.SCRUB_DATE)=DateAdd("d",-1,[pend_summary]![RPT_DATE]));

The field properties are set to Date/Time.

Please help

 

[Travis]

This query is designed to bring up all records that the Scrub_Date is one day prior to the Rpt_Date.

If that is what you want then the Query Works.

If you only want a particular Rpt_Date, then you will need to supply a criteria to limit it.