Possible use of IIF statement for dates

[corrado]

I am trying to display (in Query) a letter date that will read the column value and minus one year from that date (that date i.e. meaning today's current date).

Value(s)of Account.Letter

10 OCT 00
15 FEB 99
25 MAR 00
15 JUN 00
27 SEPT 00
01 MAR 01

I want to display any date that is greater than one year old (from todays date, or current date). So if today is 29 JAN 02, than it should display all dates stored less than 29 JAN 01 as "Review"
or be able to id it as "Review"

so from above data: it would show:
10 OCT 00
15 FEB 99
25 MAR 00
15 JUN 00
27 SEPT 00


I hope this is somewhat clear as I am new to the Access SQL thing. Any assistance would be greatly appreciated.
Corrado

 

[THoey]

If you switch to SQL View (button in top left corner changes the query view), add this to the WHERE Clause

WHERE Account.Letter < DATEADD(&quot;yyyy&quot;, -1, DATE())

That should do it... &quot;Alcohol is our friend, and it's about time we had more friends over.&quot;

Terry M. Hoey

 

[corrado]

Hello terri,

This is the SQL statement I have... I would like two where clauses so I am attempting to use AND operator towards the end.

SELECT [ACCOUNT Query].[ACCOUNT], [ACCOUNT Query].[RANK], [ACCOUNT Query].[MAJCOM], [ACCOUNT Query].[CUSTODIAN LAST NAME], [ACCOUNT].[LETTER], [CUSTODIAN INFO Query].[PHONE EXTENSION], [ACCOUNT].[UNIT]

FROM [ACCOUNT Query] INNER JOIN (ACCOUNT INNER JOIN [CUSTODIAN INFO Query] ON [ACCOUNT].[ACCOUNT]=[CUSTODIAN INFO Query].[ACCOUNT]) ON [ACCOUNT Query].[ACCOUNT]=[CUSTODIAN INFO Query].[ACCOUNT]

WHERE ((([ACCOUNT Query].[MAJCOM])=&quot;PRIMARY&quot;
AND WHERE [ACOUNT].[LETTER] < DATEADD(&quot;yyyy&quot;, -1, DATE())));

It says I am missing operator in query expression
'((([ACCOUNT.Query].[MAJCOM])=&quot;PRIMARY&quot; AND WHERE [ACCOUNT].[LETTER] < DATEADD (&quot;yyyy&quot;, -1 DATE())));

 

[corrado]

Terry,

Thank you so much- I used this below to solve the question:

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

Thanks again

DJ

 

[ebunt]

Your syntax is wrong...you don't put WHERE TWICE, only the first WHERE is necessary.