date problem

[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

 

[Adrianna]

Hmm....it works for me. I have an [AccredDate] and then I have the expiration date set to
Name[ExpireDate]
ControlSource:[AccredDate]+30

So you could either use the following in your forms and reports:
Name: SCRUB_DATE
ControlSource: =[ADJ_DATE]-1

Or you would have your query assign SCRUB_Date and expression

SCRUB_DATE: [ADJ_DATE]-1

Then run the form and report from the query

 

[RichMorrison]

You wrote
<<
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]));
>>

Depends on what you mean by "work". You are asking Access to select rows where the Scrub Date is one day less that the Rpt Date.

What is the result ?

RichM