Tom

Brilliant - that worked a treat!
Wish I'd thought of that - perhaps next time!

Many thanks
Les


"Tom van Stiphout" wrote in message
...
On Tue, 2 Mar 2010 13:59:01 -0000, "PayeDoc"
wrote:

I would use some VBA. The query is then simple:
select PersonID, GetRD(DOB) from myTable

The function GetRD must be in a standard module, and must be public:
Public Function GetRD(ByVal dob As Date) As Date
Dim rd As Date
Select Case dob
Case Is #5/5/1950#
rd = DateAdd("y", 61, dob)

Case Is #4/5/1952#
rd = #5/6/2011#

Case Is #3/5/1952#
rd = #3/6/2011#

'etc.
End Select
GetRD = rd
End Function

The beauty of the Select Case statement is that VBA will execute the
FIRST matching clause. That's why I have the date checks from youngest
to oldest.

-Tom.
Microsoft Access MVP



Hello All

I have spent most of today on this and can't get it right: I hope someone
out there can help!

I need a query that will give a person's retirement date (RD), according
to
their date of birth (DOB). The trouble is that the age at which a person
can
retire is not constant, but is instead determined according to the
following:
for a DOB earlier than 6 April 1950, the RD is on their 60th birthday
If DOB is 6 April 1950 to 5 May 1950, RD is 6 May 2010
If DOB is 6 May 1950 to 5 June1950, RD is 6 July 2010
If DOB is 6 June 1950 to 5 July 1950, RD is 6 Sep 2010
If DOB is 6 July 1950 to 5 Aug 1950, RD is 6 Nov 2010
If DOB is 6 Aug 1950 to 5 Sep 1950, RD is 6 Jan 2011
If DOB is 6 Sep 1950 to 5 Oct 1950, RD is 6 Mar 2011
etc.
etc.
until
If DOB is 6 Mar 1952 to 5 Apr 1952, RD is 6 Mar 2013
If DOB is 6 Apr 1952 to 5 May 1952, RD is 6 May 2013
for a DOB later than 5 May 1952, the RD is on their 61st birthday

That's it - and believe it or not, this is real: the UK government has
decided to 'phase in' the introduction of womens' retirement age becoming
61!

Many thanks for any help.
Leslie Isaacs