INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Jobs from Indeed

Extract month and day from date field...

Extract month and day from date field...

(OP)
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

RE: Extract month and day from date field...

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

RE: Extract month and day from date field...

(OP)
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

RE: Extract month and day from date field...

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

RE: Extract month and day from date field...

(OP)
Purrrrrrfect!

stancer

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close