The first of the month following a specific date

[bdhtexas]

I need a query that calculates a date that is the first of the month following a specific date. Example: The first of the month following the 65th birthday.

Is there any way this can be done?

 

[Mile-O]

Assuming you have already calculated the date of the 65th birthday (referred to as BDay) then:

=DateSerial(Year(DateAdd("m", 1, [BDay)), Month(DateAdd("m", 1, [BDay)), 1)

 

[bdhtexas]

Assuming you have already calculated the date of the 65th birthday (referred to as BDay) then:

=DateSerial(Year(DateAdd("m", 1, [BDay)), Month(DateAdd("m", 1, [BDay)), 1)

Not yet, I was thinking of having it calculate based on the Date of Birth field [DOB].

 

[Mile-O]

There's a list of age functions in the Code Repository then.

 

[Bat17]

as you are not really intrested in Age then adding 65 years to SJ McAbney's formula should do the trick

= DateSerial(Year(DateAdd("m", 1, [dob])) + 65, Month(DateAdd("m", 1, [dob])), 1)

HTH

Peter

edited to correct the number of years added

 

[Mile-O]

= DateSerial(Year(DateAdd("m", 1, [dob])) + 65, Month(DateAdd("m", 1, [dob])), 1)

=DateSerial(Year(DateAdd("yyyy", 65, [dob])) , Month(DateAdd("yyyy", 65, [dob]))+1, 1)

I think.

 

[bdhtexas]

What about these scenarios:

The premium due date following the 70th birthday.

DOB is 6/24/35
They turn 70 on 6/24/05
Premium is due 12/3/05
I need the calculated field to show 12/3/05

and

DOB is 11/14/35
They turn 70 on 11/14/05
Premium is due 3/12/06
I need the calculated field to show 3/12/06

 

[Bat17]

=DateSerial(Year(DateAdd("yyyy", 65, [dob])) , Month(DateAdd("yyyy", 65, [dob]))+1, 1)

I think.

Both ways should give the same result

bdhtexas

Where do the premium dates come from? what are their frequency? is the age variable now?
The question has become a whole lot more complex now

Peter

 

[bdhtexas]

Both ways should give the same result

bdhtexas

Where do the premium dates come from? what are their frequency? is the age variable now?
The question has become a whole lot more complex now

Peter

The premium due dates vary, we have to manually add them off AS400/PDA. The age varaible comes from the link below. I have completed everything except #5, #6 and #10.

http://www.access-programmers.co.uk/forums/showthread.php?t=89159

In this instance, I am thinking about just creating 2 queries, one for 65 and one for 70 and then manually add a follow-up date.

 

[bdhtexas]

A new scenario has come up

I need someone's help please. I've been trying to calculate a formula for this scenario

October 1st following the 65th Birthday

Example1: Someone turns 65, July 15, 2005, it should show Oct 1, 2005

Example2: Someone turns 65, October 15, 2005, it should show Oct 1, 2006

I have a DOB field already, I just need this date to go into my "Effective Date of Reductions" Field