Finding birthdays for the next week using DateOfBirth

[Wicksa]

The title tells it all really. I need a way to take a list of records with the field D.o.B in short format e.g. 12/10/1990 and be able to find out who's birthday it will be in the next week. I cant find a way to take the date of birth and search it to find, say the week starting 11/10 and get the dates the next week (so up to 18/10) because of the date of birth not being the current date.
sorry I'm not good at explaining things . I'm trying to say getting 12/10/2005 as the date of birth would be no problem as it would fall into the week choosen but 12/10/1990 is not the right year so doesnt appear but I want it to. so I need it to only search for the 12/10 part not the year so I can get the birthdays but I dont know how to achieve this. I'm not sure its possible but I'll probably be told its quite easy.
Typically I want the query to ask the start date of the week that the user enters, and it will find all birthdays from that date to the date 7 days on. although asking for the start date and then what date do I want to stop searching for birthdays (i.e. 'enter first date: 12/10' and 'enter second date: 30/12' then find the birthdays inbetween the dates) would probably be more practical and flexible for future use. I'm not paticularly good with ms access and although looking and searching I cant see a way to achieve this but I cant believe its not possible so any help would be appreciated very much. thanks in advanced if you help me out at all

thanks for reading

 

[neileg]

DatePart() will allow you to extract the day and month parts of your data. Alternatively, Format() will allow you to use the day and month parts together.

 

[ScottGem]

Add a column to your query with the expression:

CurrBD: DateSerial(Year(Date()),Month([DOB]),Day([DOB]))

Set the criteria for that column to:

BETWEEN Date() AND Date()+7

This will give you all birthdates between today and 7 days from today.

 

[Wicksa]

I know this is bumping and sorry but I thought I'd say thanks. I've used both of your help. ScottGem I used your solution but changed the criteria too:

Between Date() And Date()+[Please enter how many days from today you wish to be displayed: ]

So it could have any number of days, decision is made by the user. I also used the DatePart() from neileg to make an age column with expression:

Age: (DatePart("yyyy",Date())-DatePart("yyyy",[Dob]))

Which obviously displays the persons age.
cheers for the help and useful info!

 

[Mile-O]

I also used the DatePart() from neileg to make an age column with expression:

Age: (DatePart("yyyy",Date())-DatePart("yyyy",[Dob]))

Which obviously displays the persons age.

Not quite.

Consider performing it today. 23 November 2005 where a date of birth is 23 November 1980.

Age: DatePart("yyyy", Date()) - DatePart("yyyy", [DOB])
=> DatePart("yyyy", #11/23/2005#) - DatePart("yyyy", #11/23/1980#)
=> 2005 - 1980
=> 25, which is correct.

However, if we were to perform this the previous day: 22 November 2005

Age: DatePart("yyyy", Date()) - DatePart("yyyy", [DOB])
=> DatePart("yyyy", #11/22/2005#) - DatePart("yyyy", #11/23/1980#)
=> 2005 - 1980
=> 25, which is incorrect as the person is [i]still[/i] 24.

Copy the following function into a new module:

Public Function Age(dteDOB As Date, Optional SpecDate As Variant) As Integer
    Dim dteBase As Date, intCurrent As Date, intEstAge As Integer
    If IsMissing(SpecDate) Then
        dteBase = Date
    Else
        dteBase = SpecDate
    End If
    intEstAge = DateDiff("yyyy", dteDOB, dteBase)
    intCurrent = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
    Age = intEstAge + (dteBase < intCurrent)
End Function

With this function you can calculate a person's age properly. It can also work two ways:

1. Calculate a person's age as at today
2. Calculate a person's age as at a specific date

For these 2 options, call the function like so:

1. Age([DOB])
2. Age([DOB], [SpecificDate])

 

[Wicksa]

I understand what your saying but I dontthink its needed. correct me if I'm wrong though but this is my reasoning why its not.

The query returns the peoples whose birthday falls into the future. say you select 7 when asked you get all the peoples who's birthdays are in the following week (check above if you dont understand). The Age is what they will be on their birthday not their actual age that day the printout is made. Its for a club that reads them out like "and Adam is 11 in 2 days!" so I want their future age to be shown which if I'm right will always be right with my Datepart() solution. I might be wrong their but thats what I want.

I have renamed it from Age to NewAge which should stop any confusion (as long as my solution is right). While your solution would be more helpful in a way it just wont ever be needed and what I put (if right) will do exactly what they want. Again correct me if I'm wrong

 

[ScottGem]

Wicksa,
Actually what you said confirms What SJ told you to do. The expression you use for calculating age is not accurate. Your expression shows their age on the day of calculation. You really want it to be on the day of their next birthday. And you want it accurate.

 

[Wicksa]

But isnt the module going a bit over board? I tried:-

NewAge: (DateSerial(Year(Date()),Month([tblMembers.Dob]),Day([tblMembers.Dob])))- (DateSerial(Year([tblMembers.Dob]),Month([tblMembers.Dob]),Day([tblMembers.Dob])))

And as I'm sure your know that didnt do it lol

My thinking behind mine being right is because taking what sj said, if I performed it the day before and got the result of 25 years old when they are actully still 24 is what I want. I want their new age on that birthday to be shown. so by say performing it in january 06 for a date of birth 10/10/1990 I would get their new age back as 16 and not their actual age at january which is 15. Thats what I want.
By taking their DOB year away from the current year like 2005-1990 doesnt matter whenever it is performed throughout out the year it will always return 15 but they could be 14 part of the year or most depending on their birthday. Thats fine as I want their age on their birthday, not before. If I wanted to know they are 14 and will be 15 later in the year the module would be needed but because I'm only ever interested in the birthday that year it will always be the higher age so 2005-1990 will give 15 which is what I want. I cant see when it will give the wrong new age. Everyone born in 1990 will be 15 on their birthday so it works?

also I have just noticed with the selecting the date part of the query I used the criteria

Between Date() And Date()+[Please enter how many days from today you wish to be displayed: ]

which works great but say its december 30th and I want to check the week ahead so put 7 in to get the next week it wont wrap around back to january 06. This should never be a problem but I'm curious if this would be hard to correct? if it is it really isnt nessecary, more curiosity. thanks for your help again

 

[Wicksa]

Sorry I'm not great explaining things, I'm still confused to wether my query does act in the way I want it to

 

[ScottGem]

First, in the expression you posted, the first part of the expression returns their birthday in the current year. The second part returns their birthday. The Date Serial is totally superfuous in the second part. So the expression returns the number of DAYS old they were (or will be) on their birthday THIS year.

It appears that you do want the find out what age they will reach this year. Therefore try this:

Age: DatePart("yyyy", DateSerial(Year(Date()),Month([DOB]),Day([DOB]))) - DatePart("yyyy", [DOB])

This should return 15 for someone born 10/10/1990 at any time during 2005.

The Between Date and Date+7 will work even when the year wraps.