date evaluation on Day and Month Only

[Walter Steadman]

Greetings all,
Is there a way to get a query to look at Days and Months of a Date.

I have a field called Birthdate and another field called startdate

I am trying to make an update query like such:

If Date() < dateserial(year(),month([birthdate])+1, 1) update start date to
dateserial(year()-1, month([birthdate]), day ([birthdate]))

Where I am getting stuck at is in the Year part. In their Birthdate field,
it could be 8/15/1969 but I just want it to look at the 2/15 portion. The
date will be this year so if today's date, is less than the Month and Day of
the [Birthdate], I want [startdate] to be 2/15/2004

What I get now, is if the Birthdate is 2/15/1969 and I run the update query,
it puts the start date to 2/15/2004 because 2/15/1969 is in fact less than
date(). but I need that start date to be 2/15/2005 (I will make another
query that sets the dates to 2005 if the month and day have already
passed.). My intent is to run these two queries in the OnOpen event of a
form that opens with the database is opened.

 

[John Vinson]

Greetings all,
Is there a way to get a query to look at Days and Months of a Date.

I have a field called Birthdate and another field called startdate

I am trying to make an update query like such:

If Date() < dateserial(year(),month([birthdate])+1, 1) update start date to
dateserial(year()-1, month([birthdate]), day ([birthdate]))

Where I am getting stuck at is in the Year part. In their Birthdate field,
it could be 8/15/1969 but I just want it to look at the 2/15 portion. The
date will be this year so if today's date, is less than the Month and Day of
the [Birthdate], I want [startdate] to be 2/15/2004

I would get rid of the Startdate field altogether: it should NOT be
stored, since it can be calculated as desired. If Startdate should be
the person's most recent birthday anniversary, use

DateSerial(Year(Date()) - IIF(Format([birthdate], "mmdd") >
Format(Date()), "mmdd"), 0, 1), Month([birthdate]), Day([birthdate]))

John W. Vinson[MVP]