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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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 :)
 
Upvote 0
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.'
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top