How to calculate a due date to fall on the same day of the year

[easyrider]

Hello folks, here's my problem: I need to calculate the due date of something when a form opens. This due date is based on a previous date and a frequency interval, the latter two are stored in a table. The form has the following fields/controls: a textbox called MyLastDate, a combobox called MyFrequencyCombo and another textbox called MyDueDate.



The DateAdd function is currently performing this calculation and it works as expected:

   MyDueDate = DateAdd("yyyy", MyFrequencyCombo.Column(1), MyLastDate)

Now the client wants all of the due dates to fall on the same day of the year - June 30th - regardless of the value of MyLastDate. (don't ask )

For example, if MyLastDate is March 17, 2024 and MyFrequencyCombo is "2" then MyDueDate should be June 30, 2026.

I thought I remember seeing a post somewhere along the line of using constants in the DateAdd Fx, but can't seem to find it now.

Any ideas on how to accomplish this calculation?

disclaimer: I do not have the client's permission to post the database

[Welshgasman]

Use DateSerial() ?

[pbaldy]

One option would be the DateSerial() function, hard-coding the day and month arguments and incrementing the year.

[easyrider]

Thanks for the suggestions. I also used DatePart to get the year from MyLastDate then I add the value from MyFrequencyCombo. Seems to work good in initial testing so here's my revised code:

MyDueDate = DateSerial(DatePart("yyyy", [MyLastDate]) + MyFrequencyCombo.Column(1), "06", "30")

[Welshgasman]

Year() would work just as well, and what I would have used?
Then I could add 1 to it.

[pbaldy]

I would not put quotes around the 6 or the 30. They are numeric values, not text.

[easyrider]

good catch, thanks Paul

[moke123]

As Gasman pointed out year() works also

MyDueDate = DateSerial(Year([MyLastDate]) + MyFrequencyCombo.Column(1),6, 30)

[ano]

i donot understand your question
a fixed data is a fixed date so no calculation