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.

 

[Pat Hartman]

As has already been mentioned, The Format() function turns a date into a STRING from which point forward, it will act like a string rather than a date. That means that 01/01/2019 will be LESS THAN 12/31/2018. I have several apps that work both in the US and in the UK and they work because I NEVER format dates EXCEPT when I have to build SQL Strings in VBA. I rely on the local date settings in all other cases. I always use "short date" as the format property for unbound date fields and only use specific date formats when I need to reduce space or for some other reason only want to show part of a date.

Formatting is for human consumption and Format() should only be used when you actually want to return a string that LOOKS LIKE a date as when you are creating embedded SQL strings since SQL requires that string dates be in mm/dd/yyyy format.

For your forms and reports, as long as you are using bound contols, Access will know which controls are dates and will format them based on your default Windows settings so there is no need to specifically set the Format property on controls unless the control is unbound. So if you are using a form and the user enters a start and end date into an unbound control with its Format PROPERTY set to Short Date (never be specific unless you have to because you are only showing a partial date)

In a querydef, you would use
Select ... From ... Where SomeDate Between Forms!yourform!startDate and Forms!yourform!EndDate

However, in an SQL STRING built with VBA, you would use
strSQL = "Select ... From ... Where SomeDate Between #" & Me.StartDate & "# AND #" & Me.EndDate & "#"

When you want to do something with a date ALWAYS use date functions, never try to pull out parts using Left(), Right(), and Mid() functions. DateSerial is the best way to substitute a date part. Minty gave you a link in #6.

If you use the suggested DateSerial() you end up with a date rather than a string so you would not need to use cDate(). Your expression should be:

IIf(DateSerial(Year(Date()), Month(MyDate), Day(MyDate)) <= Date(), DateAdd("yyyy", 1, DateSerial(Year(Date()), Month(MyDate), Day(MyDate)), DateSerial(Year(Date()), Month(MyDate), Day(MyDate)))

This expression works entirely with dates. No strings are produced. That means it works regardless of what your Windows date format is set to. It differs from your results in one way.
1. Now() = today's date and TIME. That means that today's date will ALWAYS be less than Now() because the date expression has no time component. I changed the relational operator to be <= to compensate. If your business rule is that you want to know if the date is actually less than today, then just use the < as you did originally but with the correct date function.

To summarize.
1. Date() = today's date and Now() = today's date + time of day -- they are DIFFERENT values so make sure you use the correct function.
2. Formatting dates turns them into strings so just don't do it unless you actually want a string. If you want a date to operate like a date, use the proper date function.

 

[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....