Date Calculation in Query

[robodics]

Hi,

I am trying to do a calculation in a query, but I am not getting exactly what I want. I am definitely missing something, and I have been searching with no luck. This is what I have now, but I only want to show what can be destroyed.

Code: Select all

Destruction: IIf([Retention Expiry Date]<Date(),"Destroy","Do Not Destroy")

Alternatively, I have tried this:

Code: Select all

Destruction: [Retention Expiry Date]<Date()

I need a calculated field that shows:

If the “Retention Expiry Date” is less than the current date, show the “Retention Expiry Date,” and do not show any records that are not expired.

I hope this makes sense.

Thank you in advance for your assistance in this matter; it is greatly appreciated.

Regards,

Lisa

[HansV]

If you want to display either "Destroy" or nothing, you can use

Destruction: IIf([Retention Expiry Date]<Date(),"Destroy",Null)

If you want to display only those records that can be destroyed, do not use a calculated field, but enter a condition in the Criteria row of the Retention Expiry Date itself:

<Date()

[robodics]

Perfect! That is exactly what I was looking for.

Thank you so much!

Lisa