year date only, number or date format?

[Guest]

I want to have a field that simply allows for the year, with no month or day.
I can't figure out how make the field a Date data type. Basically, I've made
a Number field and use an input mask to keep it to four digits. It's not a
Date format though. It works just the same it seems.

If I use numbers to represent dates instead of date formats, is this a
problem? It works better for my database by separating years from months. For
example I can sort all records in a subform by year and then by month this
way.

Currently I have a table that stores the years. Then another table that
stores the Month with a code for Year that's linked to the Year table. So
instead of relying on Date formats, I have two tables that are for Years and
Months which are linked. It seems a little bit strange though.

Just curious what are the design implications and if there's a way to make
the four digit number for years into a date format.

 

[Duane Hookom]

If all you need to store is the year part of a date, I would use an integer
field. It is very easy to create an expression that compares this to a date.

DateSerial([YearIntField],1,1)
or get the Year from an actual date field
Year([RealDateField])

 

[Pat Hartman\(MVP\)]

There are numerous functions specifically for manipulating dates. You
cannot make use of any of those functions if you store year and month
separately. You cannot also take care of any of the smart groupings (by
week, by month, etc) that are available when your field is a real date. A
year is not a date. I'm not sure why you want to store it in a date data
type.

In all applications where I need to store only the year and month, I default
to the 1st of the month so I can use a date/time field. The easiest way to
do this is to train the users to enter dates as feb/2006. You need to be
very careful when entering only two of the three parts of a date. Access
"helps" you by making an intelligent choice for the missing value but it
doesn't always get it right. For example, feb/2006 will end up as
feb/1/2006 but feb/06 will end up as feb/6/2006 so make sure you understand
which shortcuts work and why.

 

[Guest]

I would like to have a field that stores only the month and day, no year.
Would I do this similar to below? I use the date in a parameter query to run
the report, so it needs to work there also.

Thanks!
Marisa

If all you need to store is the year part of a date, I would use an integer
field. It is very easy to create an expression that compares this to a date.

DateSerial([YearIntField],1,1)
or get the Year from an actual date field
Year([RealDateField])

--
Duane Hookom
MS Access MVP
--

I want to have a field that simply allows for the year, with no month or
day.
I can't figure out how make the field a Date data type. Basically, I've
made
a Number field and use an input mask to keep it to four digits. It's not a
Date format though. It works just the same it seems.

If I use numbers to represent dates instead of date formats, is this a
problem? It works better for my database by separating years from months.
For
example I can sort all records in a subform by year and then by month this
way.

Currently I have a table that stores the years. Then another table that
stores the Month with a code for Year that's linked to the Year table. So
instead of relying on Date formats, I have two tables that are for Years
and
Months which are linked. It seems a little bit strange though.

Just curious what are the design implications and if there's a way to make
the four digit number for years into a date format.

 

[Duane Hookom]

You can store this in two fields: Mth, DayOf or a single numeric field with
a value like 407. I you use a single field and need to convert it to a date,
use and expression like:
ThisYearsDate: DateSerial(Year(Date()), [MthDay] \ 100, [MthDay] Mod 100)

--
Duane Hookom
MS Access MVP
--

I would like to have a field that stores only the month and day, no year.
Would I do this similar to below? I use the date in a parameter query to
run
the report, so it needs to work there also.

Thanks!
Marisa

If all you need to store is the year part of a date, I would use an
integer
field. It is very easy to create an expression that compares this to a
date.

DateSerial([YearIntField],1,1)
or get the Year from an actual date field
Year([RealDateField])

--
Duane Hookom
MS Access MVP
--

I want to have a field that simply allows for the year, with no month or
day.
I can't figure out how make the field a Date data type. Basically, I've
made
a Number field and use an input mask to keep it to four digits. It's
not a
Date format though. It works just the same it seems.

If I use numbers to represent dates instead of date formats, is this a
problem? It works better for my database by separating years from
months.
For
example I can sort all records in a subform by year and then by month
this
way.

Currently I have a table that stores the years. Then another table that
stores the Month with a code for Year that's linked to the Year table.
So
instead of relying on Date formats, I have two tables that are for
Years
and
Months which are linked. It seems a little bit strange though.

Just curious what are the design implications and if there's a way to
make
the four digit number for years into a date format.

 

[Guest]

Thanks. I chose to store them in two fields. Now, when I query those fields
for the report, how do I show the date instead of the integer for each field
(i.e. 3 = March)? And where do I put that information?

You can store this in two fields: Mth, DayOf or a single numeric field with
a value like 407. I you use a single field and need to convert it to a date,
use and expression like:
ThisYearsDate: DateSerial(Year(Date()), [MthDay] \ 100, [MthDay] Mod 100)

--
Duane Hookom
MS Access MVP
--

I would like to have a field that stores only the month and day, no year.
Would I do this similar to below? I use the date in a parameter query to
run
the report, so it needs to work there also.

Thanks!
Marisa

If all you need to store is the year part of a date, I would use an
integer
field. It is very easy to create an expression that compares this to a
date.

DateSerial([YearIntField],1,1)
or get the Year from an actual date field
Year([RealDateField])

--
Duane Hookom
MS Access MVP
--

I want to have a field that simply allows for the year, with no month or
day.
I can't figure out how make the field a Date data type. Basically, I've
made
a Number field and use an input mask to keep it to four digits. It's
not a
Date format though. It works just the same it seems.

If I use numbers to represent dates instead of date formats, is this a
problem? It works better for my database by separating years from
months.
For
example I can sort all records in a subform by year and then by month
this
way.

Currently I have a table that stores the years. Then another table that
stores the Month with a code for Year that's linked to the Year table.
So
instead of relying on Date formats, I have two tables that are for
Years
and
Months which are linked. It seems a little bit strange though.

Just curious what are the design implications and if there's a way to
make
the four digit number for years into a date format.

 

[Duane Hookom]

"show the date"? You don't have a full date, only a month and a day of the
month. Depending on what you want to display, you might want to use
DateSerial(), MonthName(), CDate(), DateValue(), or other date functions.

--
Duane Hookom
MS Access MVP
--

Thanks. I chose to store them in two fields. Now, when I query those
fields
for the report, how do I show the date instead of the integer for each
field
(i.e. 3 = March)? And where do I put that information?

You can store this in two fields: Mth, DayOf or a single numeric field
with
a value like 407. I you use a single field and need to convert it to a
date,
use and expression like:
ThisYearsDate: DateSerial(Year(Date()), [MthDay] \ 100, [MthDay] Mod 100)

--
Duane Hookom
MS Access MVP
--

I would like to have a field that stores only the month and day, no
year.
Would I do this similar to below? I use the date in a parameter query
to
run
the report, so it needs to work there also.

Thanks!
Marisa

:

If all you need to store is the year part of a date, I would use an
integer
field. It is very easy to create an expression that compares this to a
date.

DateSerial([YearIntField],1,1)
or get the Year from an actual date field
Year([RealDateField])

--
Duane Hookom
MS Access MVP
--

I want to have a field that simply allows for the year, with no month
or
day.
I can't figure out how make the field a Date data type. Basically,
I've
made
a Number field and use an input mask to keep it to four digits. It's
not a
Date format though. It works just the same it seems.

If I use numbers to represent dates instead of date formats, is this
a
problem? It works better for my database by separating years from
months.
For
example I can sort all records in a subform by year and then by
month
this
way.

Currently I have a table that stores the years. Then another table
that
stores the Month with a code for Year that's linked to the Year
table.
So
instead of relying on Date formats, I have two tables that are for
Years
and
Months which are linked. It seems a little bit strange though.

Just curious what are the design implications and if there's a way
to
make
the four digit number for years into a date format.