Changing a year in a date then calculate.

[ECEK]

I need to do the following to a date but Im struggling:

Revise:
MyDate: Date field "dd/mm/yyyy"
I want to change the year to the current year Now() "yyyy"
This was my solution.
Expr1: Format([MyDate],"dd/mm") & "/" & Format(Now(),"yyyy")


Calculation:
Then If my revised MyDate is less than Now() add 1 year to MyDate.
If not then use MyDate

then
Expr2: IIf([Expr1]<Now(),DateAdd("yyyy",1,[Expr1]),[MyDate])

whilst Im getting results, they are incorrect.

My thoughts are that I'm incorrectly changing MyDate which therefore means that my calculation is wrong.

Thanks for any advice.

 

[Minty]

If you format a date it turns into a string.
I would use DatePart() and/or CDate() to perform the manipulations.

 

[ECEK]

I have done this calc to see if it's right and this is producing the wrong results so there is clearly something wrong. but I can't work it out why certain dates are correct and others are wrong!

Expr3: IIf([Expr1]<Now(),"Before","After")

 

[Minty]

Try CDate([Expr1]) and also try using DateSerial([Expr1])
https://support.microsoft.com/en-us...-and-for-displaying-date-time-values-in-acces

 

[ECEK]

Thanks Minty. I encased my expression within a CDate.

 

[ECEK]

Minty:
Your thoughts please:
When writing queries: Is it good practice to concatenate your calculations into one result, or create your first calculation then reference it.

ie Is it better to have one formula like in this example:
IIf(CDate(Format(Format([MyDate],"dd/mm") & "/" & Format(Now(),"yyyy"),"dd/mm/yyyy"))<Now(),DateAdd("yyyy",1,Format(Format([MyDate],"dd/mm") & "/" & Format(Now(),"yyyy"),"dd/mm/yyyy")),CDate(Format(Format([MyDate],"dd/mm") & "/" & Format(Now(),"yyyy"),"dd/mm/yyyy")))

or a series of calculations Expr1, Expr2 etc within the query?

 

[Minty]

You can't normally refer to a calculated field in the same query it is created. So the way you are doing it is correct.

You could however create the calculated field in another query then query that.

You should check the DateSerial and DatePart functions to tidy that up as well. All those Format()'s are not necessary.

 

[isladogs]

The one thing that Pat didn't mention in her very clear & detailed post was the use of the CDate function.
This will change date strings back to actual dates.

So if for any reason you have a date string it is reversible

So using Pat's 2 dates as examples, if you type this into the immediate window the result is false for the reasons Pat gave

?Format(#31/12/2018#, "mm/dd/yyyy")<Format(#1/1/2019#, "mm/dd/yyyy")

BUT if you type this, the result is true because Access is comparing the actual dates

?CDate(Format(#31/12/2018#, "mm/dd/yyyy"))<CDate(Format(#1/1/2019#, "mm/dd/yyyy"))

Obviously, its easier if you don't have to be so convoluted in your approach!

I notice Ecek has used this idea but complicated it with double Formats.
Something, I've never seen done before....