Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Partial Dates (Office 07)

    Hi,

    I have a table of historical dates which are frequently only partial dates. For example I might only know that an event happened in June 1995 Or perhaps I only know the year.

    I am hoping to sort these dates (I suspect that there may be a great number of ways of sorting these - but the exact order is not critical, as long as its consistent. So if for example all partial dates containing just 1995 come before full dates for that year or after does not particularly bother me.

    I had planned to use a date field if I had a full date, and a number field (or fields) if I had a part of the date, and then create an expression to resolve it.

    Before I start however I thought it might be a problem with an obvious solution. Unfortunately Google and groups searches have not enabled me to find anything, so any suggestions welcome.

    Thanks


    Colin

  2. #2
    Lounger
    Join Date
    Nov 2006
    Location
    Denver, Colorado, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Partial Dates (Office 07)

    I would store the data in a text field in Access. Then, to sort on the values, create a function that converts the date into a full date. For example, if the value is just 1995, convert it to 1/1/1995. If the date is 8/1995 or 8/95, convert it to 8/1/1995. The test would be to see how many slashes are in the date to determine if just the day or a month and day need to be added. With this method you would also need to add some input validation to make sure people were entering dates or you could sort anything that didn't convert to a date to the top or bottom by having your function convert unrecognized values to 1/1/0000 or 12/31/9999.

    You would call the function from within the query that retuns the data. It would be placed in the ORDER BY clause and the input parameter for the function would be the date field you want to sort on.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Partial Dates (Office 07)

    That sounds a good way.

    I can usually cope with setting this sort of thing up, but ican NEVER think of the ways to do it in the first instance.

    So thanks for that idea. Seems a great way to do it


    Colin

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Partial Dates (Office 07)

    What you could also do in a text field is to have yyyy when just the year is entered, yyyymm when the month and year is entered, then yyyymmdd when the whole date is entered. Sort on this field.

    You can have an extra field as a date field if it suits as has been described.

Posting Permissions

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