Year only in field

[Tim]

Hello, I could use a bit of help in this. While it seems
simple, maybe it is not the correct way.

In a new table, I want to have a field for the YEAR. First
guess is just create a new field and call it a number, then
enter the number of the year. Simple.

But shouldn't I be using the Date/Time and some kind of Format
in the field instead of using Number as the field type?

All I need or want will be the year, I will have no use for any
other part of the date. This field will be used for sorting and
extracting data by year.

BTW, I bring this question up as I want to make sure this field
gets filled in and it is a valid 4 digit number, that is why I
thought I should use the Date/Time field type.

Is it really as simple as number and there is no benefit from
using Date/Time as the field type?

Thank you,

Tim

 

[bhicks11 via AccessMonster.com]

Hi Tim,

If it is based on the current system date, you can populate a field with
format(now(),"yyyy") which is just the 4 digit year extracted from the system
date.

Bonnie

http://www.dataplus-svc.com

 

[Tim]

Bonnie,

Thanks for the reply. For new entries, this would be ok.
However, I will be entering data from previous years also. So I
can't default it to that. For testing purposes now, I made the
table and the field is set to NUMBER.

Tim

 

[bhicks11 via AccessMonster.com]

Oh yes, sorry Tim - I notice your question was actually what data type to use.
Unless you will be running calculations on the year I would use Text. Data
can always be converted on the fly also.

Is your problem solved?

Bonnie

http://www.dataplus-svc.com

Bonnie,

Thanks for the reply. For new entries, this would be ok.
However, I will be entering data from previous years also. So I
can't default it to that. For testing purposes now, I made the
table and the field is set to NUMBER.

Tim

If it is based on the current system date, you can populate a
field with

[quoted text clipped - 29 lines]

 

[Allen Browne]

Tim, use a Number field (size Integer), not Text.
Text is less effienct, and doesn't sort/select correctly, and can contain
non-numeric characters.

To ensure a sensible (4-digit) number is entered, set these properties on
the field:
Validation Rule: Is Null OR Between 1000 And 2999
Validation Text: Enter a 4 digit year

BTW, don't name the field YEAR. This is a function name, and in certain
contexts (forms, reports), Access will misunderstand what you are talking
about. Use something such as TheYear, BirthYear, IncidentYear.

For a list of names to refer to when designing tables, see:
http://allenbrowne.com/AppIssueBadWord.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Oh yes, sorry Tim - I notice your question was actually what data type to
use.
Unless you will be running calculations on the year I would use Text.
Data
can always be converted on the fly also.

Is your problem solved?

Bonnie

http://www.dataplus-svc.com

Bonnie,

Thanks for the reply. For new entries, this would be ok.
However, I will be entering data from previous years also. So I
can't default it to that. For testing purposes now, I made the
table and the field is set to NUMBER.

Tim

If it is based on the current system date, you can populate a
field with

[quoted text clipped - 29 lines]

Tim

 

[bhicks11 via AccessMonster.com]

I bow to Allen's opinion.

Bonnie

http://www.dataplus-svc.com

Tim, use a Number field (size Integer), not Text.
Text is less effienct, and doesn't sort/select correctly, and can contain
non-numeric characters.

To ensure a sensible (4-digit) number is entered, set these properties on
the field:
Validation Rule: Is Null OR Between 1000 And 2999
Validation Text: Enter a 4 digit year

BTW, don't name the field YEAR. This is a function name, and in certain
contexts (forms, reports), Access will misunderstand what you are talking
about. Use something such as TheYear, BirthYear, IncidentYear.

For a list of names to refer to when designing tables, see:
http://allenbrowne.com/AppIssueBadWord.html

Oh yes, sorry Tim - I notice your question was actually what data type to
use.

[quoted text clipped - 22 lines]

 

[Tim]

Tim, use a Number field (size Integer), not Text.

Text is less effienct, and doesn't sort/select correctly, and
can contain non-numeric characters.
Done.

To ensure a sensible (4-digit) number is entered, set these
properties on the field:
Validation Rule: Is Null OR Between 1000 And 2999
Validation Text: Enter a 4 digit year

Ah, I have never done something like that. This would work.

BTW, don't name the field YEAR. This is a function name, and
in certain contexts (forms, reports), Access will
misunderstand what you are talking about. Use something such
as TheYear, BirthYear, IncidentYear.

Yes, I have the field named EventYear.

Thank you Allen (and you too Bonnie) for the help in this.

Tim