More issues with Dates

[corrado]

I had sent out a message a few days ago and had responses, however this is still not bringing back correct data:
I'm only concerned with the where clause. The [ACCOUNT Query].MAJCOM works fine.

Issue: want to display only dates that are older than 1 year from todays date. (for Account.Letter Data)

Data: Account.Letter

10 Oct 2000
19 Sept 1999
2 Jan 2002
1 Feb 2002

Formula: WHERE ((([ACCOUNT Query].MAJCOM)=&quot;PRIMARY&quot AND ((ACCOUNT.LETTER)<DateAdd(&quot;yyyy&quot;,-1,Date())));

Should display Only:

19 Sept 1999

Problem: Brings back ALL data.

Any Idea's ?

Thanks

 

[corrado]

It should also bring back

10 OCT 2000

 

[tempclerk]

Is ACCOUNT.LETTER a date field? Looks like text.

Try: WHERE ((([ACCOUNT Query].MAJCOM)=&quot;PRIMARY&quot AND
(CDate(ACCOUNT.LETTER)<DateAdd(&quot;yyyy&quot;,-1,Date())));

 

[corrado]

Yes it is set as a text field. I set it to short date (date/time)and brought back correct information.

Thank you

DJ