Vanderghast

Brilliant - I can see exactly how that would work!

In fact I have used Tom's suggested function instead of the table, but I can
see that both methods would do the trick. In other circumstances where it
might be necessary to update the data periodically I can also see that it
would be easier to maintain your table than the function (a general user
could maintain the table), so I'll remember that!

Thanks again
Les


"vanderghast" vanderghast@com wrote in message
...
Have a table for this data, like:

Retirements 'table

FromD ToD Retire Age 'fields

1900.1.1 1950.4.6 null 60
1950.4.6 1950.5.6 2010.5.6 null
...
1952.4.6 1952.5.6 2013.5.6 null
1952.5.6 3000.1.1 null 61 'data


And have your SQL statement like:

SELECT *, NZ(retirements.retire, DateAdd("yyyy", retirements.age,
mytable.dob ))
FROM mytable INNER JOIN retirements
ON mytable.dob = retirements.fromD
AND mytable.dob retirement.toD



Vanderghast, Access MVP



"PayeDoc" wrote in message
...
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