Extract month and day from date field... 1

[stancer]

I have an Access database containing a Birthdate field, a DateTime field formatted as mm/dd/yyyy.
I want to extract the day and month portion of the Birthdate field and add them to the current year, e.g., if a person was born on 03/13/1952, I would want a date of 03/13/2003 or 03/13/2004, etc. to use in a date calculation. In the calculation I need to add 5 working days to the current year birthdate,e.g., 03/13/2005 + 5 working days, to set an alert where todays date is greater than the calculated current year birthdate + 5 working days.

Alert: current date(mm/dd/yyyy) - birth month(mm)/birth day(dd)/current year(yyyy) >= 6 days

Can anyone help with a formula or tips?
As before, I don't have access to any help files or books(it's a long, painful story), so you guys are the help files (probably better too!)

Thanks in advance!
stancer

 

[synapsevampire]

Try:

dateadd("yyyy",datediff("yyyy",{table.datefield},currentdate),{table.datefield})+5

This means that you get the alert 5 days after the B-Day, don't you really want 5 days before???

If so, change the +5 to a -5.

-k

 

[stancer]

Thanks so much for the formula, and yes I do want the alert after the birthday. This tells us that the person has failed to register within the allotted time window of birthdate to birthdate + 5 working days, emphasis on working days. It's a law enforcement thing...
The formula almost produced what I was looking for, just need to add working days, not just days. The person has 5 working days to register, after that the alert tells us we need to pay 'em a visit.
If I could somehow modify the calculation to add working days, I'd be real happy!

By the way, your beer for the last answer has arrived - new beer called Vapor Beer... can't you just smell the nice, cool aroma? and your wife will never know!

Thanks again in advance,
stancer

 

[synapsevampire]

Try:

dateadd("yyyy",datediff("yyyy",{Orders.Order Date},currentdate),{Orders.Order Date})
+
(
if
dayofweek(dateadd("yyyy",datediff("yyyy",{Orders.Order Date},currentdate),{Orders.Order Date})) in [2 to 6] then 7
else
if
dayofweek(dateadd("yyyy",datediff("yyyy",{Orders.Order Date},currentdate),{Orders.Order Date})) = 1 then 5
else
if
dayofweek(dateadd("yyyy",datediff("yyyy",{Orders.Order Date},currentdate),{Orders.Order Date})) = 7 then 6
)

Vaporlicious

-k

 

[stancer]

Purrrrrrfect!

stancer