Birthday Querie

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

Hi there

I have in the database the ID numbers of people. How can i
go on a specific day to look who's birthday is it today?
Ex. ID number : 8112018525369
His birthday is 1 December 1981

What do i type in the querie field to find out if the
birthday of this person is 1 December (which it is)

Thanks in advance
 
To search date fields for a specific date, you would need to add a new
column to your query. Use the datepart function to pull out the month and
day. In the criteria, enter the month and day for which you are searching.

Rick B


Hi there

I have in the database the ID numbers of people. How can i
go on a specific day to look who's birthday is it today?
Ex. ID number : 8112018525369
His birthday is 1 December 1981

What do i type in the querie field to find out if the
birthday of this person is 1 December (which it is)

Thanks in advance
 
For example, add columns to your queries with the following data...

BirthMonth: DatePart("m",[Birthdate])
BirthYear: DatePart("d",[Birthdate])

This would give you a column with the month and a column with the day. In
the criteria you could specify the months and days you wish to pull.

Rick B



Hi there

I have in the database the ID numbers of people. How can i
go on a specific day to look who's birthday is it today?
Ex. ID number : 8112018525369
His birthday is 1 December 1981

What do i type in the querie field to find out if the
birthday of this person is 1 December (which it is)

Thanks in advance
 
Hi Rick

Appreciate your effort. Thank you, just tell me should i
then put in the birthdate of the person also on 2 separate
coloumns (one coloumn for the day and one for the month)
or can i put it as an ID number in(811201 5242 288)

Craig Meyer
 
Ummm. an id number? Why not just enter it as a date? Access does all the
work of turning it into a number in the background. You don't need to do
any conversion. Just set your field as a Date/Time field and enter it like
you would anywhere else.

And No, you don't need two columns. That is the whole point of adding those
columns to the query. It breaks the date up for you. In your table you
would just have a normal ole date entered that you could pull to reports,
queries, forms, etc.

Rick B



Hi Rick

Appreciate your effort. Thank you, just tell me should i
then put in the birthdate of the person also on 2 separate
coloumns (one coloumn for the day and one for the month)
or can i put it as an ID number in(811201 5242 288)

Craig Meyer
 
Hi Rick

What did you then mean by this:
"...for example, add coloumns to your queries with the
following data...

BirthMonth: DatePart(.......
BirthYear: DatePart(.....

This would give you a coloumn with the month & a coloumn
with the day...."

Please excuse me if i don't catch it.

Craig
 
Just what i said. Add columns to your QUERY. Your table will still just
have one field, called BirthDate. It will be formatted as a date field.
when you add a new employee to your database, you will enter their Name,
Address, etc. and their birthdate (as a date).

You can print this data, display it on forms, etc.

If you wish to pull a list of people who have birthdays on a specific day,
you will create a query. In the query, you will pull their name and their
birthdate. In order to limit it to only certain days, you will add two new
columns to your query as previously instructed.

This has nothing to do woth your table or the overall design of your
database. It is simply a temporary way of filtering the data that you pull
for a specific purpose (I would assume you will be printing a report of all
employees with a birthdate on a specific date).

It seems to me (unless you have a large number of employees) That you could
pull all the employees with birthdates in a particular MONTH and print them
in order or Month/Day.


Rick B


Hi Rick

What did you then mean by this:
"...for example, add coloumns to your queries with the
following data...

BirthMonth: DatePart(.......
BirthYear: DatePart(.....

This would give you a coloumn with the month & a coloumn
with the day...."

Please excuse me if i don't catch it.

Craig
 
Hi there

I have in the database the ID numbers of people. How can i
go on a specific day to look who's birthday is it today?
Ex. ID number : 8112018525369
His birthday is 1 December 1981

What do i type in the querie field to find out if the
birthday of this person is 1 December (which it is)

Thanks in advance
Click to expand...

In addition to Rick's suggestion, one way is to create a calculated
field in a query by typing:

HappyHappy: DateSerial(Year(Date()), Month([birthdate]),
Day([birthdate]))

where [Birthdate] is a date/time field containing the birthdate.

AHA! I see the problem. Your ID is what's called an "intelligent key"
- you're packing non-ID information into the ID. Not generally
recommended! A birthdate IS DATA and should NOT be stored in an ID; it
should be stored in a birthdate field, and the ID should just be a
unique ID without extraneous information in it!

If you're going to keep this (unwise, IMO) design, then the expression
would be

HappyHappy: DateSerial(Year(Date()), Val(Mid([ID Number], 3, 2)),
Val(Mid([ID Number], 5, 2)))

This field will contain this year's anniversary date, and you can use
a criterion on it of

#1-Dec#

to find who has a December 1 birthday, or

BETWEEN Date() AND Date() + 7

to find out who has birthdays coming up in the next week.
 
Back
Top