Results 1 to 4 of 4
  1. #1
    Join Date
    May 2016
    Posts
    1

    Unanswered: Creating a range of Dates in Date/Time formatting

    Hello, Everyone!

    This is my first question. I'm currently working on a database and I want to include a Field for years active. I'm having trouble creating a yyyy format that would allow for both ranges. I'm a student in my first databases class. I don't know a lot about access yet and I want to make my database stand out; this is above and beyond the project requirments.

    My hopes would be that the field would look like this (2007-Present) or (2007-2016) to be in each cell. I'm assuming the formatting would look like yyyy - yyyy but it doesn't work for some reason. I want to keep the formatting as Date/Time.

    The current input mask that I am using is "("0000\-0000")";0;_

    I have searched all over google and the access help function to no avail. Maybe I'm not searching for the appropriate terms.

    Anything that could point me in the right direction or any help would be greatly appreciated!

    Thank you!
    Last edited by pc8807; 05-14-16 at 22:56.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Databases separate the storage of information from the presentation. So part of the problem is how you store the data. Part of it is how you present the data. A prime example of this is dates and times. Dbs tend to store dates as a number, but depending on what / how you use the date / time data they can represrnt that ass, say 16 May 2016 12:34:56, or 2016/5/16 12:34 PM or May 2016, or just 2016

    From a data perspective what do you mean by 'present'. Ferinstance you may be modelling a membership process, you have members. You know when they first joined, you know when they last paid their (annual) membership and from that you know when their membership expires. So a consumer of that information may want to see that represented as from-to, eg 2004-2008 or 2004-present or 'current' or 4 (years) depending on what you want to see. But from a data perspective its important you store the information in the right type of column (s). Numbers in an aporopriate numeric column, text or alphanumeric in a string/text column. But temporal data (time and or date) in a datetime column.

    In yoyr case its not ckear if your data is truly a date time value or purely a numeric value (ie you are storing the year). Either could be appropriate.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by pc8807 View Post
    ...I'm currently working on a database and I want to include a Field for years active...My hopes would be that the field would look like this (2007-Present) or (2007-2016) to be in each cell...
    The problem is that you're violating a cardinal rule of relational databases...you're trying to store more than one piece of information in a single Field (not Cell...BTW, 'cell' is a spreadsheet term) and you should have one Field for your 'from' year and a second Field for your 'to' year.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ..its not a problem if its used SOLELY for display purposes AND is derived form a start & end date, but yes it is a big problem if this is being STORED as a single value.

    it could be derived from the date values using, say a function
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •