If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need help with Between dates on birthday calculation
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 |
#2
|
|||
|
|||
Need help with Between dates on birthday calculation
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" wrote: 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 |
#3
|
|||
|
|||
Need help with Between dates on birthday calculation
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" wrote: 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" wrote: 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 |
#4
|
|||
|
|||
Need help with Between dates on birthday calculation
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" wrote: 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" wrote: 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" wrote: 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 |
#6
|
|||
|
|||
Need help with Between dates on birthday calculation
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#7
|
|||
|
|||
Need help with Between dates on birthday calculation
Steve
Nice touch with the date diff but criteria didnt work. so i tried -5 And 5 Works great for dates but not birthdays as 1-7-1968 is further back than 5 days any other ideas would be appreciated "Steve" wrote: 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 "Barry A&P" wrote in message ... 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 . |
#8
|
|||
|
|||
Need help with Between dates on birthday calculation
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" wrote in message ... 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" wrote: 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 |
#9
|
|||
|
|||
Need help with Between dates on birthday calculation
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 Barry A&P wrote: Steve Nice touch with the date diff but criteria didnt work. so i tried -5 And 5 Works great for dates but not birthdays as 1-7-1968 is further back than 5 days any other ideas would be appreciated "Steve" wrote: 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 "Barry A&P" wrote in message ... 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 . |
#10
|
|||
|
|||
Need help with Between dates on birthday calculation
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" wrote: 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 Barry A&P wrote: Steve Nice touch with the date diff but criteria didnt work. so i tried -5 And 5 Works great for dates but not birthdays as 1-7-1968 is further back than 5 days any other ideas would be appreciated "Steve" wrote: 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 "Barry A&P" wrote in message ... 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 . . |
Thread Tools | |
Display Modes | |
|
|