Year only -haw hard can this be!!!

[Timoty]

Year only -How hard can this be!!!

I want a field in a table that is a date but that is the year only.
I only want this because I want to be able to filter my charts to show one or a span of years for comparison. I have criteria in a query that states between [forms].[charts].[begin] and [forms].[charts].[end] and the corresponding form that my user puts the desired begginning and ending dates into before pressing buttons that bring up the charts based on the query.

My problem is that I can't even get the table to take a year only date. If I leave the date field as text the between clause in the query won't work. I tried it as a number with >= etc. and that didn't work either.

How hard can something this simple be?

Please help me oh cyberspace wizards.

 

[Matt Greatorex]

Store the date as a full date (e.g. 27/01/2006), but display it to the user as a year by using Format(tablename.fieldname,"YYYY").

You can then set an input mask on the field, to ensure that they can enter just a four digit year or just a two digit year. Alternatively, leave it off altogether and do the comparison on just the year part of whatever they type in.

Hope that makes sense.

 

[FoFa]

Two things. You can define it as a INTEGER field and just put the date in.
But I think a better approach would be to use YEAR([yourdate]) BETWEEN 2001 AND 2003 (for a range) or YEAR([yourdate]) = 2003 for a specific year

 

[Timoty]

Thanks for the quick reply.
Just to clarify FoFa....
better approach would be to use YEAR([yourdate]) BETWEEN 2001 AND 2003 (for a range) or YEAR([yourdate]) = 2003 for a specific year

Would this be put into the table query...?

 

[mhartman]

Hello:

These would be your options:

1) Make your table field a number field and limit the entry to 4 characters.
2) Make your table field a date and extract the year portion of the date using the Year Function as shown below.

Dim MyDate, MyYear
MyDate = #February 12, 1969# ' Assign a date.
MyYear = Year(MyDate) ' MyYear contains 1969.

Regards
Mark

 

[Timoty]

I am far better in Excel where year is simply yyyy
What is the correct way to set the input mask for yyyy only in access as simply putting yyyy doesn't seem to work.

 

[Timoty]

Mark...I have played very little with code. Where exactly do I put the year function within a table?
The code I have played with is always within a form or report. Usually a form.

So I make the table field a 4 character number and then put:
Dim MyDate, MyYear
MyDate = #February 12, 1969# ' Assign a date.
MyYear = Year(MyDate) ' MyYear contains 1969.
where exactly?

 

[FoFa]

Store the whole date. If you ONLY want the year, just use an INTEGER data type. Then you can use masking, etc. with ease.

 

[Timoty]

Thanks to all.
Sorry I couldn't respond yesterday as I had to run out of the office.
I went with the INTEGER option. It appeared to be the simplest way.
However, once again, I certainly appreciate all of your options.

Tim

 

[The_Doc_Man]

Don't do it ANY of those ways. Use a query wizard to build a table that does a selection for you, then drive your charts off the query, not the tables.

In the query, you can use a FORMAT( [actualdate], "yyyy" ) to force the issue of year-only dates.