Birthday Query

  • Thread starter Thread starter Randy Fritz
  • Start date Start date
R

Randy Fritz

Hello NG

Access97. I am trying to create a report query that shows who is having
a birthday - between starting date and ending date - and then show what the
persons age would be on their birthday - Now I have a table with the
birthdates and by way of dropping the year I know if it falls between the
starting date and ending date using the datevalue function - but it doesn't
work if I go June 1,2004 to June 1,2005 because it sees only June 1 to June
1. How Can I write the query so that the years that are chosen are used to
determine birthdays within the 2 dates. I do not care if there are multiple
records becuase the time span covers multiple years I would actually like
that - Going 1/1/2004 to 12/31/2007 having 4 records for a persons birthday
would be fine. but I cannot seem to make any query work like this.

Randy
 
To get multiple entries for each person, the years have to come from
somewhere.

1. Create a table with one field:
- Field Name TheYear
- Field Type Number
Make "TheYear" primary key. Save.

2. Create a record for each year in the range you need.

3. Create a query into this table and your other table. If you see any line
joining the 2 queries in the upper pane of query design, delete it. The
unjoined tables generate a Cartesian Product (i.e. every possible
combination).

4. In a fresh column of the query design grid, Field row, enter:
Birthday: DateSerial([TheYear], Month([DOB], Day([DOB]))
assuming that your "date of birth" field is named "DOB".
In the Criteria row beneath this field, enter the limiting dates.
 
TY Allen,

That does what I needed it to do.

Randy

Allen Browne said:
To get multiple entries for each person, the years have to come from
somewhere.

1. Create a table with one field:
- Field Name TheYear
- Field Type Number
Make "TheYear" primary key. Save.

2. Create a record for each year in the range you need.

3. Create a query into this table and your other table. If you see any line
joining the 2 queries in the upper pane of query design, delete it. The
unjoined tables generate a Cartesian Product (i.e. every possible
combination).

4. In a fresh column of the query design grid, Field row, enter:
Birthday: DateSerial([TheYear], Month([DOB], Day([DOB]))
assuming that your "date of birth" field is named "DOB".
In the Criteria row beneath this field, enter the limiting dates.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Randy Fritz said:
Access97. I am trying to create a report query that shows who is having
a birthday - between starting date and ending date - and then show what the
persons age would be on their birthday - Now I have a table with the
birthdates and by way of dropping the year I know if it falls between the
starting date and ending date using the datevalue function - but it doesn't
work if I go June 1,2004 to June 1,2005 because it sees only June 1 to June
1. How Can I write the query so that the years that are chosen are used to
determine birthdays within the 2 dates. I do not care if there are multiple
records becuase the time span covers multiple years I would actually like
that - Going 1/1/2004 to 12/31/2007 having 4 records for a persons birthday
would be fine. but I cannot seem to make any query work like this.
Click to expand...
Click to expand...
 
Back
Top