<DateSerial(Year(Date()) query criteria

Question

Hello,

I have the below column in a query which adds a useful life in years, example 5, 10, etc to the Acquisition Date in a YYYY-MM format. All is great.

Now I need to add criteria to this column so that only records with a "Target Replace Date" older than July 1st of the current year come up. When I run the query below the criteria doesn't work. I still get dates newer than 7/1/2017, example 12/5/2018, etc. Can you help me fix this so that only dates older than July 1 of the current year appear in the query results?

Column Expression       Target Replace Date: Format(DateAdd("yyyy",[tbl_ComboBox_Categories].[Useful Life],[tbl_Master].[Acquisition Date]),"yyyy-mm")

Criteria       <DateSerial(Year(Date()),7,1)

Answer 1

I'm not sure why you did not post this in your earlier thread on the same subject, but as you are returning a formatted date of yyyy-mm  in the column, restricting it on a date value will not work.  You should restrict a separate column in the query, whose expression is:

DateAdd("yyyy",[tbl_ComboBox_Categories].[Useful Life],[tbl_Master].[Acquisition Date])

Uncheck the 'show' checkbox for the column so that it is not returned by the query, and apply the same criterion as before:

<DateSerial(Year(Date()),7,1)

You are now comparing a date value with a date value, rather than with a string expression, which is what the Format function returns.

Answer 2

Your expressions work perfectly. Thanks.