Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Lao PDR
    Posts
    15

    Question Unanswered: Listing Years Between Two Dates

    Here is my problem...

    I have a table of Projects, each with a start and end date. I want to create a query that will list all of the years which these dates include. I have made the following union query, but run into a little problem.

    SELECT [StartYear] AS [Year] FROM [Projects_FiscalYears]
    UNION SELECT [EndYear] AS [Year] FROM [Projects_FiscalYears]
    ORDER BY [Year];

    which produces the following records:

    Year
    1997
    1999
    2000
    2001
    2002
    2003
    2004
    2005
    2006
    2007
    2008
    2009
    2010

    Since there are no projects which either start or end in 1998, it's not in the list although projects are ongoing during that year. That's my problem. How can i get a list of all the years from the Min(StartDate) to the Max(EndDate)?

    Any insight would be greatly appreciated.

    Thanks,
    Justin

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by Justin84
    Here is my problem...

    I have a table of Projects, each with a start and end date. I want to create a query that will list all of the years which these dates include. I have made the following union query, but run into a little problem.

    SELECT [StartYear] AS [Year] FROM [Projects_FiscalYears]
    UNION SELECT [EndYear] AS [Year] FROM [Projects_FiscalYears]
    ORDER BY [Year];

    which produces the following records:

    Year
    1997
    1999
    2000
    2001
    2002
    2003
    2004
    2005
    2006
    2007
    2008
    2009
    2010

    Since there are no projects which either start or end in 1998, it's not in the list although projects are ongoing during that year. That's my problem. How can i get a list of all the years from the Min(StartDate) to the Max(EndDate)?

    Any insight would be greatly appreciated.

    Thanks,
    Justin
    Hi Justin,
    Just by looking at what you stated here, it seems you are getting exactly what you should be. You stated it pulls records that have Start/End dates for each project. If it doesn't come out when you run the query it is most likely because you didn't input a Start/End date for that year/period. Being as the others did come through that would seem to be the case for me.
    Now, did you by chance have a start/end date for those projects in 1998 and they DID NOT come through in the query? If so, then you do have a problem. Do you have anything in your database that would Restrict certain projects from qualifying in the query? Like in my program, it lists all of the Projects that open only. Meaning I have a CheckBox to set them to closed, thereby only the ones not check come through in the query.
    Check that out and let me know. Also, are you using this query to populate a DialogBox? You know, you put the dates in this form to pull up the records you wish in another form? If not I have possibly a better way to pull out this data for you as well. I have a form set as a DialogBox with two Text fields. One for BeginningDate and one for EndingDate. Once I put those dates in and click the button, a form opens with those records only.

    let me know where you stand.
    have a nice one,
    BUD

  3. #3
    Join Date
    Feb 2004
    Location
    Lao PDR
    Posts
    15

    Looking for all years within range

    Bud, thanks for you reply. It is working on that level, but what I'm after is a list of all the years in which projects are ongoing, not just the years in which projects start or end.

    I need this list for a subsequent query which will give me the number and value of projects in any of the listed years.

    The only other thing i can think of is to creat a table of numbers covering any possible years (maybe 1990 - 2020) and select from it based on the Min() and Max() in the project start and end dates. But for some reason i suspect there is a slicker solution...

    Thanks,
    Justin

Posting Permissions

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