By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,515 Members | 1,363 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,515 IT Pros & Developers. It's quick & easy.

List people whose 40th birthday is approximately one month from now?

P: n/a
I have a db that stores people and their birth dates. On any given day, I
need to know who has a 40th birthday one month from now. I will only be
running the query on week days but of course some peoples 40th birthdays
will fall on Saturday and Sunday, I may also be out of the office on certain
days. So I need to "cast a net" to catch all birthdays "approximately" one
month from now, but that "approximation needs to be predictable.

How would you do this?

I can write all the queries and VBA and stuff to achieve the result I just
don't know what the formula should be. Some options I thought of where:

1. If the current date falls within the first week of this month show me all
40th birthdays that fall within the first week of next month. But I can see
problems with that.

2. Return all 40th Birthdays that fall exactly one month from today and on
every other day of that week Sun-Sat inclusive.

Other than these two imperfect solutions I am a bit stuck.

Thanks,

Paul
Mar 13 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
A person's birthday this year is:
DateSerial(Year(Date()), Month([BirthDate], Day([BirthDate]))

If the birthday has already happened, you want to add 1 year, so:
NextBirthday: DateSerial(Year(Date()) -
(DateSerial(Year(Date()), Month([BirthDate], Day([BirthDate])) <
Date()),
Month([BirthDate], Day([BirthDate]))

Type that into the Field row in query design.
In the criteria row under that, enter whatever you want, e.g.:
Between Date() And Date() + 30

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul H" <pa**@nospam.comwrote in message
news:tK******************************@eclipse.net. uk...
>I have a db that stores people and their birth dates. On any given day, I
need to know who has a 40th birthday one month from now. I will only be
running the query on week days but of course some peoples 40th birthdays
will fall on Saturday and Sunday, I may also be out of the office on
certain days. So I need to "cast a net" to catch all birthdays
"approximately" one month from now, but that "approximation needs to be
predictable.

How would you do this?

I can write all the queries and VBA and stuff to achieve the result I just
don't know what the formula should be. Some options I thought of where:

1. If the current date falls within the first week of this month show me
all 40th birthdays that fall within the first week of next month. But I
can see problems with that.

2. Return all 40th Birthdays that fall exactly one month from today and on
every other day of that week Sun-Sat inclusive.

Other than these two imperfect solutions I am a bit stuck.
Mar 13 '07 #2

P: n/a
Think you would also want a second criteria row (thus an "And" condition) of
DateDiff("yyyy", [BirthDate], [Date]) = 40

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:45**********************@per-qv1-newsreader-01.iinet.net.au...
>A person's birthday this year is:
DateSerial(Year(Date()), Month([BirthDate], Day([BirthDate]))

If the birthday has already happened, you want to add 1 year, so:
NextBirthday: DateSerial(Year(Date()) -
(DateSerial(Year(Date()), Month([BirthDate], Day([BirthDate])) <
Date()),
Month([BirthDate], Day([BirthDate]))

Type that into the Field row in query design.
In the criteria row under that, enter whatever you want, e.g.:
Between Date() And Date() + 30

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul H" <pa**@nospam.comwrote in message
news:tK******************************@eclipse.net. uk...
>>I have a db that stores people and their birth dates. On any given day, I
need to know who has a 40th birthday one month from now. I will only be
running the query on week days but of course some peoples 40th birthdays
will fall on Saturday and Sunday, I may also be out of the office on
certain days. So I need to "cast a net" to catch all birthdays
"approximately" one month from now, but that "approximation needs to be
predictable.

How would you do this?

I can write all the queries and VBA and stuff to achieve the result I
just don't know what the formula should be. Some options I thought of
where:

1. If the current date falls within the first week of this month show me
all 40th birthdays that fall within the first week of next month. But I
can see problems with that.

2. Return all 40th Birthdays that fall exactly one month from today and
on every other day of that week Sun-Sat inclusive.

Other than these two imperfect solutions I am a bit stuck.

Mar 13 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.