Adding days to a date

[CesarAustin]

Hi... hope someone can help me... what i'm trying to do is add "Days" to a date... example: if i have 1/1/2021... i want to add 1096 days (which is 3 years) to that date... so the new date would be 1/1/2024... i totally suck at SQL.. so i was hoping there was something i can add to the "Update to:" hope someone can help

 

[CesarAustin]

well.. I feel dumb.... I checked and my "EOLDate" column was formatted as "ShortText"... I changed it to "Date/Time" and that worked... but if someone has a better way to do the same thing... that would be great

 

[Micron]

Not necessarily better for this case but you could look into DateAdd function for next time. You didn't say there was a "Data type mismatch error" so the quotes around 1096 were a dead give away. If you don't provide them in criteria for text fields, Access will add them.

 

[JonXL]

Well it looks like you found the chief cause of the problem but to your second issue of whether there's a better way, the answer is 'yes'.

I'd use DateDiff() in this case as that will let you specify an interval of years instead of trying to 'fake it' with (365×3) - which cannot accommodate leapyears.

DateDiff Function - Microsoft Support

Use the DateDiff function in Access to returns a Variant (Long) specifying the number of time intervals between two specified dates.

support.microsoft.com

 

[JonXL]

Good grief. I need to pay better attention to what I'm saying and what I'm thinking...

DateDiff() tells you how many units of the specified interval are between two dates. When you want to add or subtract an interval to a date, use DateAdd()...

DateAdd Function - Microsoft Support

support.microsoft.com

 

[Micron]

When you want to add or subtract an interval to a date, use DateAdd()...

Wish I'd said that.