Birthday Club Database

MrRadio915

New Member
Joined
Dec 20, 2005
Messages
24
I do a Kids Radio program every week in which I do a birthday club.
Presently I have the data in an excel workbook and have individual columns for M, D and Yearof birth. By entering a new year in one cell, and referencing that cell in calculated cell on EVERY LINE, I can arrive at an age for the child. I print a postcard and a list every week after sorting, extracting and creating a new workbook EVERY week for that week's birthdays.

At a radio station I used to work for, I did all that in access simply by entering a "EnterShowDate" variable and Access did the rest.

I've had quite a bit of success designing simple queries, but the query that did the selection and calculation of age based on the Child's birthdate and the "EnterShowDate" has eluded me. (A microsoft employee helped me over the phone one day for free). Since then, I discovered that that type of help is taboo for MS employees.

Given the variables of:
BIRTHDATE - the month, day and year of the child's birth -and-
ENTERSHOWDATE - the day that begins the birthday club (entered as a variable when the query is initiated)

1. Could someone help me develop a query that would extract the proper birthday club entries based on the month and day of the birthdate?
The Birthday Club is read on a Saturday and runs through the following Friday so the range would be BETWEEN (EnterShowDate and EnterShowDate+6) inclusive.

2. Could someone help me figure out a way to calculate the age of the child based on the year of EnterShowDate and BIRTHDATE? The calculation needs to work over the transition of Dec 31 to Jan1.

Given the proper syntax, I can design the resulting reports (my script and the postcards), I'm just too dense to get the syntax of working with dates (believe me I've tried).

Any help would be appreciated.

This is positively the COOLEST forum on the web in a very geeky way.

-MrRadio915

PS - This would be helpful for any organization who wishes to recognize birthdays in a timely manner.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi

In your query design screen, add the table and the birth date fields (and any other fields you want to see). While in the design screen click on Query -> Parameters -> add 'Enter Show Date' (without the speech marks) and set the data type to date.

Create a new field in your query, using the following syntax :
Code:
NextDate : IIf(Month([BirthDate])<Month([Enter Show Date]), DateSerial(1+Year([Enter Show Date]), Month([BirthDate]), Day([BirthDate])), DateSerial(Year([Enter Show Date]), Month([BirthDate]), Day([BirthDate])))

And use the following criteria for this new field :
>=[Enter Show Date] And <=DateAdd("d",6,[Enter Show Date])

This works ok for Access 2000 and 2003 and accomodates the end of year. This shows all birth dates inside the relevant week but be aware that the year is incorrect for this new field, so you could hide this new field but still show the actual birth date field and it would still work ok.

HTH, Andrew :)
 

MrRadio915

New Member
Joined
Dec 20, 2005
Messages
24
Great! So Glad to have this help. I'll try it soon.

BTW the Birthday Club is on the web at www.am91.org Saturday Mornings at 8:55 Denver, CO time.

-MRradio915
 

MrRadio915

New Member
Joined
Dec 20, 2005
Messages
24
So glad to be operating with the power of Access again. I have successfully created the query and am selecting birthday clubbers and calculating ages accurately. I've tested the query and it works well. I'm using the following code to calcualte the age of the child.

Code:
Age: DateDiff("yyyy",[Birthdate],IIf(Month([BirthDate])<Month([EnterShowDate]),DateSerial(1+Year([EnterShowDate]),Month([BirthDate]),Day([BirthDate])),DateSerial(Year([EnterShowDate]),Month([BirthDate]),Day([BirthDate]))))

This calculates the age of the child as of his/her birthdate and works over the break in years.

I have one more question and will continue to work on this one.

In the report, I'd like to have the birthdays separated by the day of the current week beginning with the date entered as [EnterShowDate]. Example:

Saturday, December 31 (This is the date entered as [EnterShowDate]
Johnny Jones (DOB 12/31/2000) is 5
Sally Smith (DOB 12/31/1995 is 10

Sunday, January 1
Billy Thompson (DOB 1/1/1999) is 7
Carl Ballard (DOB 1/1/1996) is 10

Monday, January 2
Zach Lisbon (DOB 1/2/1991) is 15
Joe Farmer (DOB 1/2/1990) is 16
etc.

Ages ascending on each day of the current week.


Again, this is the coolest forum on the 'net.'
 

CT Witter

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
Here is what I did:

Query:
Code:
PARAMETERS [Enter Show Date] DateTime;
SELECT tblBirthday.Name, IIf(Month([BirthDate])<Month([Enter Show Date]),DateSerial(1+Year([Enter Show Date]),Month([BirthDate]),Day([BirthDate])),DateSerial(Year([Enter Show Date]),Month([BirthDate]),Day([BirthDate]))) AS NextDate, DateDiff("yyyy",[Birthdate],[NextDate]) AS Age, tblBirthday.BirthDate
FROM tblBirthday
WHERE (((IIf(Month([BirthDate])<Month([Enter Show Date]),DateSerial(1+Year([Enter Show Date]),Month([BirthDate]),Day([BirthDate])),DateSerial(Year([Enter Show Date]),Month([BirthDate]),Day([BirthDate]))))>=[Enter Show Date] And (IIf(Month([BirthDate])<Month([Enter Show Date]),DateSerial(1+Year([Enter Show Date]),Month([BirthDate]),Day([BirthDate])),DateSerial(Year([Enter Show Date]),Month([BirthDate]),Day([BirthDate]))))<=DateAdd("d",6,[Enter Show Date])));

Then startup the report wizard.
1) Select based on a query
2) add all fields from above query
3) Click Next
4) Grouping by NextDate. Select grouping options in lower left.
5) Group Interval By Day.
6) Click Next
7) Sort Order Age - Ascending.
8) Click Next
9) Stepped. Next, Next, Finish

When you run the report you are prompted for the 'Enter Show Date'. The report then displays.

HTH,
CT
 

MrRadio915

New Member
Joined
Dec 20, 2005
Messages
24
Wow! What a Review...

It's been about 5 years since I've played with Access and the folks who have responded have been a great help.

I found that by including NextDate from the query that I could then sort by that field.

Now all I have to do is take what the Wizard does and make it look like I want it to look.

THANKS LOADS.

-MrRadio915
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,099
Members
412,441
Latest member
kelethymos
Top