DateAdd

[Pat Hartman]

Like i have a date "30-Jul-2024", i want to give a me date "29-Jun-2027".

What is the rule in words? Add 35 months and subtract 1 day? What does the subtract 1 day do? What happens if the day is 1? then you go back to the last day of the previous month?

 

[Gasman]

I seriously cannot believe this is what you want to do? :-(
Unless the term is a stupid number, you would probably add the interval, years,month,days, hours,minutes whatever and subtract 1 ?
So a bus pass starts from 30/06/24 and last for 10 years. Expiry date is 24+10 less 1 day, so 29/06/34?

 

[moi]

What is the rule in words? Add 35 months and subtract 1 day? What does the subtract 1 day do? What happens if the day is 1? then you go back to the last day of the previous month?

Hi pat,

Sorry for late reply.. you got it right, that's how they schedule the start to end of the monthly payment of the land buyer.. if the buyer decided to go to a 36 month.. then it will start the monthly payment on 30-Jul-2024 (the day it signed the contract) and it will/should finished/paid on or before 29-Jun-2027... that is how they count, so I did not question on it.. whether it is the correct way or not, let it be, as they are using that way way before i joined the company..

 

[moi]

I seriously cannot believe this is what you want to do? :-(
Unless the term is a stupid number, you would probably add the interval, years,month,days, hours,minutes whatever and subtract 1 ?
So a bus pass starts from 30/06/24 and last for 10 years. Expiry date is 24+10 less 1 day, so 29/06/34?

Hello Gasman,

It is not the way I wanted, but it is their way to count the start and end of the monthly payment of the buyer.. It is their calendar to schedule the payment of the buyer.. so yes a bit stupid but that is their way.

 

[Pat Hartman]

OK, if it is number of months minus 1 day, then

DateAdd("m", NumMonths, StartDate) - 1

 

[CJ_London]

as suggested in post #18

 

[Pat Hartman]

as suggested in post #18

The two expressions are different. I have no idea which is correct. Although mine does date + x months - 1 day. Yours does x-1 months + date

 

[Josef P.]

Note:
For the readability of the business rule, you could write it like this:
= DateAdd("d", -1, DateAdd("m", [terms] - 1, [ctsreleased])
In terms of content, this is the same in Access as:
= DateAdd("m", [terms] - 1, [ctsreleased]) - 1 (see #25)

With the variant DateAdd(..) - 1, I have to think about the internal conversion of date values in Access/ACE so that I understand the - 1.
With the 1st variant, I only have to read what is being done.

BTW: it is relevant whether the months are added first and then the day is subtracted or vice versa.

Dim d As Date
d = #3/1/2024#
Debug.Print "first: +35 mon then: -1 day = "; DateAdd("d", -1, DateAdd("m", 36 - 1, d))
Debug.Print "first: -1 day then: +35 mon = "; DateAdd("m", 36 - 1, DateAdd("d", -1, d))

=>
first: +35 mon then: -1 day = 31.01.2027
first: -1 day then: +35 mon = 29.01.2027

 

[Gasman]

TBH if I am just adding/subtracting days I just use mydate +/- mydays.

 

[Josef P.]

TBH if I am just adding/subtracting days I just use mydate +/- mydays.

If this is only 1 expression, then it will probably be easy to understand due to the variable names.

The 2nd example in #28 reads like this to me:
Add [term of contract] - 1 month and then when I read it, the -1 is somewhat pushed away, so that I have to think: "and what is the -1 doing here?"
Of course, anyone who knows VBA or SQL understands the content of this line.
In the first variant with 2x DateAdd, I can understand the content more quickly because I can see immediately that values are being added twice.

But these are just my thoughts, for readability. Maybe for others it's the other way around.

 

[ebs17]

Remember #11: DateSerial is not a DateAdd, but it is extremely flexible to meet all your needs. You just have to know what you want.