Need help with Between dates on birthday calculation

  • Thread starter Thread starter Barry A&P
  • Start date Start date
B

Barry A&P

I have a little birthday notification popup that shows clients that have or
have had a birthday in the next 5 or last 5 days from the current date and
some other junk to concatenate the notification..

Because i used Between Day(Date())-5 And Day(Date())+5)) the criteria does
not work in the beginning or end of the month. as it would look for the 35th
day in january instead of 5th of febuary. is there a correct way to do this.

SELECT ClientInfo.ClientID, ClientInfo.FirstName, ClientInfo.LastName,
Format([DOB],"dddd"", ""mmm"", ""d") AS bday, Month([DOB]) AS bdmonth,
Day([DOB]) AS bdday,
DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)
AS Age, IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),"Will Be ","Just
Turned ") AS Agetext,
[age]+IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0) AS bdayage
FROM ClientInfo
WHERE (((Month([DOB]))=Month(Date())) AND ((Day([DOB])) Between
Day(Date())-5 And Day(Date())+5))
ORDER BY Month([DOB]), Day([DOB]);

Thanks
Barry
 
You do not care what month it is so try this --
WHERE Day([DOB]) Between Day(Date())-5 And Day(Date())+5
 
Karl
When i remove the month clause i get all birthdays between jan 4th and 11th
feb 4th and 11th mar 4-11th ect..

if today was the 31st and my WHERE clause uses Day(Date())+5 it wont
advance to the 5th of next month.. almost like i need to somehow add the 5
somewhere else like Day(Date()+5) and some kind of IIf for the Month like
month(date())+ IIf(month(date()+5) > Month(date()),1,0)

I feel like i am almost there. but maybe on the wrong track..

PS:
I no longer get notifications of responses to my posts and have to search
all over because they are also not at the top of the subject list.. i checked
my e-mail address and Spam. any ideas.

Thanks
Barry

KARL DEWEY said:
You do not care what month it is so try this --
WHERE Day([DOB]) Between Day(Date())-5 And Day(Date())+5

--
Build a little, test a little.


Barry A&P said:
I have a little birthday notification popup that shows clients that have or
have had a birthday in the next 5 or last 5 days from the current date and
some other junk to concatenate the notification..

Because i used Between Day(Date())-5 And Day(Date())+5)) the criteria does
not work in the beginning or end of the month. as it would look for the 35th
day in january instead of 5th of febuary. is there a correct way to do this.

SELECT ClientInfo.ClientID, ClientInfo.FirstName, ClientInfo.LastName,
Format([DOB],"dddd"", ""mmm"", ""d") AS bday, Month([DOB]) AS bdmonth,
Day([DOB]) AS bdday,
DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)
AS Age, IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),"Will Be ","Just
Turned ") AS Agetext,
[age]+IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0) AS bdayage
FROM ClientInfo
WHERE (((Month([DOB]))=Month(Date())) AND ((Day([DOB])) Between
Day(Date())-5 And Day(Date())+5))
ORDER BY Month([DOB]), Day([DOB]);

Thanks
Barry
Click to expand...
Click to expand...
 
My error for not checking beyond this month.
Try this --
WHERE Format([DOB], "y") Between Format(Date()-5, "y") And Format(Date()+5,
"y")

This will give you a problem around the end and start of year.

--
Build a little, test a little.


Barry A&P said:
Karl
When i remove the month clause i get all birthdays between jan 4th and 11th
feb 4th and 11th mar 4-11th ect..

if today was the 31st and my WHERE clause uses Day(Date())+5 it wont
advance to the 5th of next month.. almost like i need to somehow add the 5
somewhere else like Day(Date()+5) and some kind of IIf for the Month like
month(date())+ IIf(month(date()+5) > Month(date()),1,0)

I feel like i am almost there. but maybe on the wrong track..

PS:
I no longer get notifications of responses to my posts and have to search
all over because they are also not at the top of the subject list.. i checked
my e-mail address and Spam. any ideas.

Thanks
Barry

KARL DEWEY said:
You do not care what month it is so try this --
WHERE Day([DOB]) Between Day(Date())-5 And Day(Date())+5

--
Build a little, test a little.


Barry A&P said:
I have a little birthday notification popup that shows clients that have or
have had a birthday in the next 5 or last 5 days from the current date and
some other junk to concatenate the notification..

Because i used Between Day(Date())-5 And Day(Date())+5)) the criteria does
not work in the beginning or end of the month. as it would look for the 35th
day in january instead of 5th of febuary. is there a correct way to do this.

SELECT ClientInfo.ClientID, ClientInfo.FirstName, ClientInfo.LastName,
Format([DOB],"dddd"", ""mmm"", ""d") AS bday, Month([DOB]) AS bdmonth,
Day([DOB]) AS bdday,
DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)
AS Age, IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),"Will Be ","Just
Turned ") AS Agetext,
[age]+IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0) AS bdayage
FROM ClientInfo
WHERE (((Month([DOB]))=Month(Date())) AND ((Day([DOB])) Between
Day(Date())-5 And Day(Date())+5))
ORDER BY Month([DOB]), Day([DOB]);

Thanks
Barry
Click to expand...
Click to expand...
Click to expand...
 
Hi Barry,

In the query design grid in the first blank field, put the following
expression:
ShowPopup= DateDiff("d",BirthDate,Date())
Put the following expression in the criteria of this field:
<-5 OR >5

You then need code to open your popup for each client returned by this
query.

Steve
(e-mail address removed)
 
Karl
WHERE Format([DOB], "y") Between Format(Date()-5, "y") And
Format(Date()+5,"y") gave me all of the 2010 birthdays

Month([DOB]) Between Month(Date()-30) And Month(Date()+30) And Day([DOB])
Between Day(Date()-30) And Day(Date()+30)

seems to be getting closer but i get a few october bdays and i still see a
Jan & Dec Issue (i upped to 30 days just to increase my results)

maybe ill have to go to 10 OR clauses because "Between 29 and 8 doesnt give
good results"

maybe i should reformat my DOB's to the current year??

it seems like this would be a common thing on the newsgroup. ill keep
looking.

Thanks
Barry

KARL DEWEY said:
My error for not checking beyond this month.
Try this --
WHERE Format([DOB], "y") Between Format(Date()-5, "y") And Format(Date()+5,
"y")

This will give you a problem around the end and start of year.

--
Build a little, test a little.


Barry A&P said:
Karl
When i remove the month clause i get all birthdays between jan 4th and 11th
feb 4th and 11th mar 4-11th ect..

if today was the 31st and my WHERE clause uses Day(Date())+5 it wont
advance to the 5th of next month.. almost like i need to somehow add the 5
somewhere else like Day(Date()+5) and some kind of IIf for the Month like
month(date())+ IIf(month(date()+5) > Month(date()),1,0)

I feel like i am almost there. but maybe on the wrong track..

PS:
I no longer get notifications of responses to my posts and have to search
all over because they are also not at the top of the subject list.. i checked
my e-mail address and Spam. any ideas.

Thanks
Barry

KARL DEWEY said:
You do not care what month it is so try this --
WHERE Day([DOB]) Between Day(Date())-5 And Day(Date())+5

--
Build a little, test a little.


:

I have a little birthday notification popup that shows clients that have or
have had a birthday in the next 5 or last 5 days from the current date and
some other junk to concatenate the notification..

Because i used Between Day(Date())-5 And Day(Date())+5)) the criteria does
not work in the beginning or end of the month. as it would look for the 35th
day in january instead of 5th of febuary. is there a correct way to do this.

SELECT ClientInfo.ClientID, ClientInfo.FirstName, ClientInfo.LastName,
Format([DOB],"dddd"", ""mmm"", ""d") AS bday, Month([DOB]) AS bdmonth,
Day([DOB]) AS bdday,
DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)
AS Age, IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),"Will Be ","Just
Turned ") AS Agetext,
[age]+IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0) AS bdayage
FROM ClientInfo
WHERE (((Month([DOB]))=Month(Date())) AND ((Day([DOB])) Between
Day(Date())-5 And Day(Date())+5))
ORDER BY Month([DOB]), Day([DOB]);

Thanks
Barry
Click to expand...
Click to expand...
Click to expand...
 
Steve
Nice touch with the date diff but criteria didnt work.
so i tried
-5 And <5
Click to expand...
Works great for dates but not birthdays as 1-7-1968 is further back than 5
days

any other ideas would be appreciated

Steve said:
Hi Barry,

In the query design grid in the first blank field, put the following
expression:
ShowPopup= DateDiff("d",BirthDate,Date())
Put the following expression in the criteria of this field:
<-5 OR >5

You then need code to open your popup for each client returned by this
query.

Steve
(e-mail address removed)


Barry A&P said:
I have a little birthday notification popup that shows clients that have or
have had a birthday in the next 5 or last 5 days from the current date and
some other junk to concatenate the notification..

Because i used Between Day(Date())-5 And Day(Date())+5)) the criteria does
not work in the beginning or end of the month. as it would look for the
35th
day in january instead of 5th of febuary. is there a correct way to do
this.

SELECT ClientInfo.ClientID, ClientInfo.FirstName, ClientInfo.LastName,
Format([DOB],"dddd"", ""mmm"", ""d") AS bday, Month([DOB]) AS bdmonth,
Day([DOB]) AS bdday,
DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)
AS Age, IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),"Will Be ","Just
Turned ") AS Agetext,
[age]+IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0) AS bdayage
FROM ClientInfo
WHERE (((Month([DOB]))=Month(Date())) AND ((Day([DOB])) Between
Day(Date())-5 And Day(Date())+5))
ORDER BY Month([DOB]), Day([DOB]);

Thanks
Barry
Click to expand...


.
Click to expand...
 
Barry,

Let's try...

'PLEASE CHECK FIELD NAMES
'Untested

SELECT ClientInfo.ClientID, ClientInfo.FirstName, ClientInfo.LastName,
ClientInfo.DOB, Day([DOB]) AS DayOfBirth, Month([DOB]) AS MonthOfBirth,
IIf(Month([DOB])>Month(Date()),"Will be " &
Year(Date())-Year([DOB]),IIf(Month([DOB])<=Month(Date()),"Just Turned " &
Year(Date())-Year([DOB]))) AS Age
FROM ClientInfo
WHERE (((ClientInfo.DOB) Is Not Null) AND ((Day([DOB])) Between
Day(Date())+5 And Day(Date())-5))
ORDER BY ClientInfo.DOB;

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

KARL DEWEY said:
You do not care what month it is so try this --
WHERE Day([DOB]) Between Day(Date())-5 And Day(Date())+5

--
Build a little, test a little.


Barry A&P said:
I have a little birthday notification popup that shows clients that have
or
have had a birthday in the next 5 or last 5 days from the current date
and
some other junk to concatenate the notification..

Because i used Between Day(Date())-5 And Day(Date())+5)) the criteria
does
not work in the beginning or end of the month. as it would look for the
35th
day in january instead of 5th of febuary. is there a correct way to do
this.

SELECT ClientInfo.ClientID, ClientInfo.FirstName, ClientInfo.LastName,
Format([DOB],"dddd"", ""mmm"", ""d") AS bday, Month([DOB]) AS bdmonth,
Day([DOB]) AS bdday,
DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)
AS Age, IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),"Will Be ","Just
Turned ") AS Agetext,
[age]+IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0) AS bdayage
FROM ClientInfo
WHERE (((Month([DOB]))=Month(Date())) AND ((Day([DOB])) Between
Day(Date())-5 And Day(Date())+5))
ORDER BY Month([DOB]), Day([DOB]);

Thanks
Barry
Click to expand...
Click to expand...
 
Calculate the birthday using the following:

DateSerial(Year(Date()),Month([DateOfBirth]),Day ([DateOfBirth]))

Set the criteria for the Birthday column to:
Between Date() and Date() + 60 OR
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 60

In SQL that would look something like:
Select PersonName,
DateSerial(Year(Date()),Month([DateOfBirth]),Day ([DateOfBirth])) as Birthday
FROM tablename
WHERE
DateSerial(Year(Date()),Month([DateOfBirth]),Day ([DateOfBirth]))
Between Date() and Date() + 60
OR
DateSerial(Year(Date()),Month([DateOfBirth]),Day ([DateOfBirth]))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 60

If you want plus or minus x days then you can change the +60 to +5 and
subtract 5 from the first date in the between clauses.



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Steve
Nice touch with the date diff but criteria didnt work.
so i tried
-5 And <5
Click to expand...
Works great for dates but not birthdays as 1-7-1968 is further back than 5
days

any other ideas would be appreciated

Steve said:
Hi Barry,

In the query design grid in the first blank field, put the following
expression:
ShowPopup= DateDiff("d",BirthDate,Date())
Put the following expression in the criteria of this field:
<-5 OR >5

You then need code to open your popup for each client returned by this
query.

Steve
(e-mail address removed)


Barry A&P said:
I have a little birthday notification popup that shows clients that have or
have had a birthday in the next 5 or last 5 days from the current date and
some other junk to concatenate the notification..

Because i used Between Day(Date())-5 And Day(Date())+5)) the criteria does
not work in the beginning or end of the month. as it would look for the
35th
day in january instead of 5th of febuary. is there a correct way to do
this.

SELECT ClientInfo.ClientID, ClientInfo.FirstName, ClientInfo.LastName,
Format([DOB],"dddd"", ""mmm"", ""d") AS bday, Month([DOB]) AS bdmonth,
Day([DOB]) AS bdday,
DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)
AS Age, IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),"Will Be ","Just
Turned ") AS Agetext,
[age]+IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0) AS bdayage
FROM ClientInfo
WHERE (((Month([DOB]))=Month(Date())) AND ((Day([DOB])) Between
Day(Date())-5 And Day(Date())+5))
ORDER BY Month([DOB]), Day([DOB]);

Thanks
Barry
Click to expand...

.
Click to expand...
Click to expand...
 
Thank you everybody for your help

And the winner is.....

John
heres what seems to work
SELECT clientinfo.FName, DateSerial(Year(Date()),Month([Dob]),Day([Dob])) AS
Birthday
FROM clientinfo
WHERE (((DateSerial(Year(Date()),Month([Dob]),Day([Dob]))) Between Date()-30
And Date()+30
Or (DateSerial(Year(Date()),Month([Dob]),Day([Dob]))) Between
DateAdd("yyyy",-1,Date()-30) And DateAdd("yyyy",-1,Date()+30)
Or (DateSerial(Year(Date()),Month([Dob]),Day([Dob]))) Between
DateAdd("yyyy",1,Date()-30) And DateAdd("yyyy",1,Date()+30)));

I added another OR to your suggeation with a +1 dateAdd and that seems to
cover the issue.. Thank you

I found a great post by Dale Fye under "Find anniversary of a date" in
queries it deffinately gets Honorable Mention but i was hoping not to add a
module. so i didnt use it

'Since you didn't indicate that you want to identify which anniversary (1,
'10, etc), another alternative is to create a function and pass it the
'values. I function I have for this purpose, which I have provided below.
'There are a couple of advantages to this function:
'
'1. you are not limited to next month, you just have to pass it any two
'dates.
'2. much easier to read and maintain
'
'To call the function in a query just add a column:
'
'IsAnniversary([Birthday], #1/1/06#, #1/31/06#)
'
'If you actually want to know which anniversary, then you can change the
'IsAnniversary function to an Anniversary function by changing the Return
'data type to variant (you probably want to return a Null if the anniversary
'does not fall between the two dates) and determining the number of years
'difference using the datediff( ) function in the sections of code where I am
'currently setting IsAnniversary to True.

Public Function IsAnniversary(SomeDate As Variant, _
StartDate As Date, _
EndDate As Date) As Boolean

Dim intDay As Integer
Dim intMonth As Integer

If IsNull(SomeDate) Then
IsAnniversary = False
Exit Function
End If

intDay = Day(SomeDate)
intMonth = Month(SomeDate)
If DateSerial(Year(StartDate), intMonth, intDay) >= StartDate _
And DateSerial(Year(StartDate), intMonth, intDay) <= EndDate Then
IsAnniversary = True
ElseIf DateSerial(Year(EndDate), intMonth, intDay) >= StartDate _
And DateSerial(Year(EndDate), intMonth, intDay) <= EndDate Then
IsAnniversary = True
Else
IsAnniversary = False
End If

End Function


John Spencer said:
Calculate the birthday using the following:

DateSerial(Year(Date()),Month([DateOfBirth]),Day ([DateOfBirth]))

Set the criteria for the Birthday column to:
Between Date() and Date() + 60 OR
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 60

In SQL that would look something like:
Select PersonName,
DateSerial(Year(Date()),Month([DateOfBirth]),Day ([DateOfBirth])) as Birthday
FROM tablename
WHERE
DateSerial(Year(Date()),Month([DateOfBirth]),Day ([DateOfBirth]))
Between Date() and Date() + 60
OR
DateSerial(Year(Date()),Month([DateOfBirth]),Day ([DateOfBirth]))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 60

If you want plus or minus x days then you can change the +60 to +5 and
subtract 5 from the first date in the between clauses.



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Steve
Nice touch with the date diff but criteria didnt work.
so i tried
-5 And <5
Click to expand...
Works great for dates but not birthdays as 1-7-1968 is further back than 5
days

any other ideas would be appreciated

Steve said:
Hi Barry,

In the query design grid in the first blank field, put the following
expression:
ShowPopup= DateDiff("d",BirthDate,Date())
Put the following expression in the criteria of this field:
<-5 OR >5

You then need code to open your popup for each client returned by this
query.

Steve
(e-mail address removed)


I have a little birthday notification popup that shows clients that have or
have had a birthday in the next 5 or last 5 days from the current date and
some other junk to concatenate the notification..

Because i used Between Day(Date())-5 And Day(Date())+5)) the criteria does
not work in the beginning or end of the month. as it would look for the
35th
day in january instead of 5th of febuary. is there a correct way to do
this.

SELECT ClientInfo.ClientID, ClientInfo.FirstName, ClientInfo.LastName,
Format([DOB],"dddd"", ""mmm"", ""d") AS bday, Month([DOB]) AS bdmonth,
Day([DOB]) AS bdday,
DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)
AS Age, IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),"Will Be ","Just
Turned ") AS Agetext,
[age]+IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0) AS bdayage
FROM ClientInfo
WHERE (((Month([DOB]))=Month(Date())) AND ((Day([DOB])) Between
Day(Date())-5 And Day(Date())+5))
ORDER BY Month([DOB]), Day([DOB]);

Thanks
Barry

.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top