IF Statement Inquiry

[kparadise]

I have a DATE field "Target Date", which I have values for.

I want to create a new DATE column in my access query called "Validation Date".

The "Validate Date" will equal the last day of the year 12/31/2016, 2017, 2018, etc., and is based off of the "Target Date". If the "Target Date" is within the year 2014, the "Validation Date" will be the following year end date, 12/31/2015.

I.E. If the "Target Date" is any day within 2015....the "Validation Date" will be 12/31/2016.
I.E If the "Target Date" is any day within 2016....the "Validation Date" will be 12/31/2017.

 

[Joe4]

Try this:

Validation_Date: DateSerial(Year([Target Date])+1,12,31)

 

[kparadise]

Thank you!

 

[HiTechCoach]

Since this is a calculation based on data in the table it really should not be stored. Following the rules of Data Normalization (Best Practices) you would calculate the validation date as needed.

 

[Joe4]

Since this is a calculation based on data in the table it really should not be stored. Following the rules of Data Normalization (Best Practices) you would calculate the validation date as needed.

It sounds to me like they weren't trying to, based on the following line from the original question.

I want to create a new DATE column in my access query called "Validation Date".

 

[HiTechCoach]

Jpe4.

You are probably (hopefully) correct.

I read "...create a new DATE column in my access query..." and jumped the gun thinking the query was an UPDATE query. It could mean "...create a new DATE column in my access SELECT query..." or "...create a new DATE column in my access UPDATE query..." since the specific type of query was not mentioned. I took it as the latter.

I like to create a Public User Defined Function (UDF) in a code module to handle the calculation. This way it can easily be used in a query, form, and report, and even a macro. I would pass a date and return the validation date. If the calculation needs updated it is easily changed in a single place.