Birthday format

[Jennifer]

We track birthdays with month/date but do not track the year. When we input
into the form it is mm/dd only but going into the table it automatically puts
the current year. Now trying to run a birthday list for January 2010 no
birthdays are showing up. How can I either change the year or just have
mm/dd in my table?

 

[Duane Hookom]

You can pull the month of the field using Month([YourDateField]). Then just
set the criteria under this column to 1 or reference a control on a form like:
=Forms!frmYourFormName!cboMonthNumber

 

[Tom Wickerath]

Hi Jennifer,

If the field you are using is a Date/Time data type, then this will include
the year--there is no getting around that. One way to accomplish your goal is
to enter the person's actual birth date, for example 14-Feb-1968. Then, it
would be relatively easy to query for a birthday list for a given month.

The alternative, if you do not wish to store the year, is to change the data
type to Text. This will require being consistent when querying the data, for
example entering or not entering leading zeros, and the order mm/dd versus
dd/mm. Really, the easiest method is to enter the actual birth dates if you
have these available. This will allow you to easily calculate age at any
given time, as well as produce a birthday list for a given month.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Al Campagna]

Jennifer,
I think that usually it's best to capture the persons BirthDate.
Ex. 10/17/65
That way, you can calculate age... as well as birthdays.

Given a field [BirthDate]
Try a query with a criteria of...
Month(BirthDate) = 1
would yield all January BirthDates... regardless of year.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Jennifer]

Can I query for just the month/day? and leave the year in the table. We
have over 2000 people entered and several people that input the data so
looking for the easiest/quickest solution.

 

[Jennifer]

This is the formula in the date field on the form. Can I remove the "year"
portion?


=DateSerial(Year(Date()),Month(Date())+1,1)

 

[Jennifer]

We are not supposed to know the age. We celebrate employee birthdays, but we
are not HR so cannot know the year.

Jennifer,
I think that usually it's best to capture the persons BirthDate.
Ex. 10/17/65
That way, you can calculate age... as well as birthdays.

Given a field [BirthDate]
Try a query with a criteria of...
Month(BirthDate) = 1
would yield all January BirthDates... regardless of year.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

We track birthdays with month/date but do not track the year. When we
input
into the form it is mm/dd only but going into the table it automatically
puts
the current year. Now trying to run a birthday list for January 2010 no
birthdays are showing up. How can I either change the year or just have
mm/dd in my table?

.

 

[Tom Wickerath]

I suppose you could enter a ficticious year, such as 2009, for all employees.
But just to be clear, the fieldname should probably be Birthday instead of
Birthdate.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

We are not supposed to know the age. We celebrate employee birthdays, but we
are not HR so cannot know the year. __________________________________________

Jennifer,
I think that usually it's best to capture the persons BirthDate.
Ex. 10/17/65
That way, you can calculate age... as well as birthdays.

Given a field [BirthDate]
Try a query with a criteria of...
Month(BirthDate) = 1
would yield all January BirthDates... regardless of year.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life." __________________________________________

We track birthdays with month/date but do not track the year. When we
input
into the form it is mm/dd only but going into the table it automatically
puts
the current year. Now trying to run a birthday list for January 2010 no
birthdays are showing up. How can I either change the year or just have
mm/dd in my table?

 

[Douglas J. Steele]

No. Dates must be complete dates.

 

[Tom Wickerath]

Do you really need to include the day in your criteria? If you are interested
in a listing of all birthdays in a given month, then I would think you would
not want to include a day criteria. But yes, you can include a day criteria
if you really want to and ignore the year as well.

I think Al Campagna's suggestion to use the Month function is what will
likely work out best for you. You could create a form that includes a combo
box as a part of this functionality. The Row Source for the combo box could
be a Value List, along the lines of:

1, "January"; 2, "February"; 3, "March"; .....12, "December"

with column count = 2 and bound column = 1.

Your query could then pick up the criteria from this combo box:

Field: Month(BirthDate)
Criteria: Forms!NameOfForm!NameOfComboBox

Perhaps this example will be helpful. This zipped sample includes a Word
document written by Michael Hernandez, along with a small test database:

http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Jeff Boyce]

Jennifer

Were you aware that you can store actual birthdates, but DISPLAY only m/d?

You could use a query to find all birthdays where
Month([YourBirthdayDateField] = 1 (January), or = 2 (February), ...,
then simply display m/d.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[xiaozhou]

We track birthdays with month/date but do not track the year. When we
input
into the form it is mm/dd only but going into the table it automatically
puts
the current year. Now trying to run a birthday list for January 2010 no
birthdays are showing up. How can I either change the year or just have
mm/dd in my table?