date/year data filtering.

[Krazy Darcy]

In my database two of the fields relate to date. 1 is for year (4 digit)
while the other is the date of creation (date format in form dd/mm/yy ie
23/1/2007).

Is there a way to automatically fill in the year field when the date is
entered?

Also I have some queries that retrieve records from a user specified year.
How would i use that query on just the year part of the date field. this is
why I currently have a seperate year field. (this part is less important than
the first part of this forum query)

 

[Douglas J. Steele]

There's no reason to have both fields. (In fact, it's actually a violation
of database normalization principles to have both)

Simply store the date of creation in the table. Then, create a query that
includes all the fields from the table, and include a computed field that
uses the Year function on the date of creation. Use the query wherever you
would otherwise have used the table.

Alternatively, don't bother creating the year field, but have your query to
retrieve based on a user-specified year use

BETWEEN DateSerial([What Year?], 1, 1) AND DateSerial([What Year?], 12, 31)

as its criteria.

 

[fredg]

On Thu, 22 Feb 2007 12:26:01 -0800, Krazy Darcy wrote:

Answers in line, below.

In my database two of the fields relate to date. 1 is for year (4 digit)
while the other is the date of creation (date format in form dd/mm/yy ie
23/1/2007).

A Date format of dd/mm/yy
does not display 23/1/2007, it displays 23/1/07.

Is there a way to automatically fill in the year field when the date is
entered?

You can delete that [Year] field from your table (you don't need it,
and besides, Year is an Access keyword and should not be used as a
field name).
All you need is just the [DateOfCreation] field.

You can display just the year anytime you wish.
To just display the year, set the Format property of the date field to
yyyy

Also I have some queries that retrieve records from a user specified year.
How would i use that query on just the year part of the date field. this is
why I currently have a seperate year field. (this part is less important than
the first part of this forum query)

In a query, add a new column to the grid.
TheYear:Year([DateField])
as criteria on this column, write:
[What Year]

When prompted, enter the wanted year, i.e. 2006

The Year() function returns just the year value of a Date field.

 

[Krazy Darcy]

Thanks verry much.
It worked like a charm.

I copied my "all artworks for specified year" query, went into design mode,
removed the [what year] from the year column and pasted your script into the
date column pasted the supplied script into. I left the year field in but as
confirmation that it was working.

I will have to redo a crosstab query I have as it relies on the year field.
for that I will need to sort by month. The crosstab query gives me a year
by year, month by month breakdown of how many art works were created so I can
see where creativity peaks and wanes

Thanks for your time
(would have replied sooner but had to turn place upside down for spare
keyboard batteries)

There's no reason to have both fields. (In fact, it's actually a violation
of database normalization principles to have both)

Simply store the date of creation in the table. Then, create a query that
includes all the fields from the table, and include a computed field that
uses the Year function on the date of creation. Use the query wherever you
would otherwise have used the table.

Alternatively, don't bother creating the year field, but have your query to
retrieve based on a user-specified year use

BETWEEN DateSerial([What Year?], 1, 1) AND DateSerial([What Year?], 12, 31)

as its criteria.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

In my database two of the fields relate to date. 1 is for year (4 digit)
while the other is the date of creation (date format in form dd/mm/yy ie
23/1/2007).

Is there a way to automatically fill in the year field when the date is
entered?

Also I have some queries that retrieve records from a user specified year.
How would i use that query on just the year part of the date field. this
is
why I currently have a seperate year field. (this part is less important
than
the first part of this forum query)