DateSerial Anniversary Dates (1 Viewer)

Lochwood

Registered User.
Local time
Today, 15:39
Joined
Jun 7, 2017
Messages
130
Sleepless night over this one, Help me out Guys.

We have clients who have a fixed anniversary date. This date never changes but comes up for renewal every year and is stored in an anniversary table linked by the form. Every year we create a new record for the client, run through the processes and finish with a completed date which might be 4 weeks either side of the anniversary date. Example: Anniversary Date: 6th July 2007. Process completed date 17th July 2018. New Due date: 6th July 2019.

In our scenario we have some clients not updated for say 3 years or so with no records created for them in that time but the DateSerial reports next year as a due date. Example: last completed date: 9th July 2014. Due Date 6th July 2019.. HELP!!!

Completed Date fieled is in client table and anniversary date is in anniversary table.
 

June7

AWF VIP
Local time
Today, 14:39
Joined
Mar 9, 2014
Messages
5,423
So what date do you want to show for those returning clients?

How do you handle Anniversary date of February 29?
 

Lochwood

Registered User.
Local time
Today, 15:39
Joined
Jun 7, 2017
Messages
130
Feb 29th has never come up :)

Example: Anniversary Date: 26th April 2012 Client has 6 records in a Sub report last record was created 2018 with a completed date field of 17th April 2018 so Due Date should be 26th April 2019 but DateSerial looks at anniversary date and returns next years date of 26th April 2020 regardless. i would like to see if there is a way dateserial or another function can look at completed date and add a year based on anniversary date.
 

isladogs

MVP / VIP
Local time
Today, 22:39
Joined
Jan 14, 2017
Messages
18,186
One possible solution:
Code:
Day(AnniversaryDate) & "/" & Month(AnniversaryDate) & "/" & Year(CompletedDate)+1
 

Lochwood

Registered User.
Local time
Today, 15:39
Joined
Jun 7, 2017
Messages
130
Genius! works a treat. is there an easy way to change the formatting date... currently displays as 1/1/2019.. no option to change format to Medium date.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:39
Joined
Jan 23, 2006
Messages
15,364
You can surround Colin's solution with

Format (colinsStuff,"Medium Date"), the result will be a string datatype.
 

isladogs

MVP / VIP
Local time
Today, 22:39
Joined
Jan 14, 2017
Messages
18,186
Is this what you want?
Code:
FORMAT(Day(AnniversaryDate) & "/" & Month(AnniversaryDate) & "/" & Year(CompletedDate)+1,"dddddd")

EXAMPLE 18/06/2020 is shown as 18 June 2020. This is long date
Medium date format would be 18-Jun-20
 

Lochwood

Registered User.
Local time
Today, 15:39
Joined
Jun 7, 2017
Messages
130
Got it working. Thanks for your help much appreciated!!
 

isladogs

MVP / VIP
Local time
Today, 22:39
Joined
Jan 14, 2017
Messages
18,186
You're welcome. Good luck with the rest of your project.
 

Users who are viewing this thread

Top Bottom