Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 21st, 2006, 12:35 PM
Authorized User
 
Join Date: Dec 2003
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default Year format

Hello,
I often create a database with year fields. The question is : what data type would you use ? Text, number or date/time.
And what are the advantages/inconvenients?
Thanks alot,
Tachyophan

 
Old May 21st, 2006, 12:42 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

If all you are storing is the year, I would personally just use a number.

Mike
EchoVue.com
 
Old May 23rd, 2006, 06:56 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I had heard that the rule of thumb is if you are not carrying out arithmetic functions on a number value, that the default field type is text, even if you will always be storing a number in the field.

Usually you would seperate dates into Year, Month and Day in the old server databases. IMHO, I would make this a date field and then pull the year value as needed from mm/dd/yyyy. Even if it is a text field, you can still do DateDiff operations if you date the field when carrying out the function.

HTH

mmcdonal
 
Old May 23rd, 2006, 11:35 PM
Authorized User
 
Join Date: Apr 2006
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

For me the answer is it depends on my application (perhaps there is a theoretical best). If I am going to write an app that contains alot of uses of number of days between events (datediff) than I will store in a date format. If I am importing or exporting data from outsides sources I will often use the format of those sources, ie if my data comes in text, I use text, if date then date etc.

If I have a fair number of records I need to sort on months, I tend to use numbers so that I can easily sort them in order. I also have a tendency to use 2 seperate fields, one for month and one for years. Seems to make the coding easier as I have to use fewer formulas etc. (Assuming the app is simple and I typically run most of my reports on a monthly basis)

 
Old May 24th, 2006, 06:34 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I guess it depends on what you do most often with this data. DO you have any other questions, Tachyophan?

mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Week of Year kiwibey ASP.NET 2.0 Professional 0 June 3rd, 2007 11:14 PM
To get current year darshil XSLT 3 May 21st, 2007 06:46 AM
How to calculate "last year"? ihsail Linux 1 December 1st, 2005 06:03 PM
Year Validation jputhra SQL Server 2000 1 December 10th, 2004 10:03 AM
Fiscal Year jemacc SQL Server 2000 4 October 7th, 2004 03:05 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.