Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Return all dates within a range

Discussion in 'Information Technology' started by Joe, Jul 28, 2006.

  1. Joe

    Joe
    Expand Collapse
    Guest

    Hi,

    I've seen this done as a .udf in SQL Server before, but I would like to
    know if I can do it in access.

    I would like to specify a start date and end date, then return each
    date value within the specified range. Here's an example:

    Start Date : 6/1/06
    End Date: 6/10/06

    Result set:
    6/1/06
    6/2/06
    6/3/06
    .....
    6/10/06

    Can someone help me?

    Thanks,

    Joe
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Ontario Police Review Arrest Of Sikh Man Whose Turban Wasn't Returned - CTV News Breaking News Jul 16, 2016
    Movies Ashdoc's short movie review---Tanu weds Manu returns Theatre, Movies & Cinema Jun 6, 2015
    Zoroastrianism Parsis pray for return of the scavenger bird Interfaith Dialogues Aug 28, 2013
    Steve Jobs' Return to Apple Business, Lifestyle & Leisure Mar 28, 2013
    Opinion Campaign to reward homeless man who returned diamond engagement ring hits $175,000 - and that total Breaking News Mar 7, 2013

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Create a table named CountNumber with a number field, Long Integer named
    CountNUM. Fill table with number one through the maximum spread you will
    have.

    Use this query.
    SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
    FROM CountNumber
    WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter end date])));


    "Joe" wrote:

    > Hi,
    >
    > I've seen this done as a .udf in SQL Server before, but I would like to
    > know if I can do it in access.
    >
    > I would like to specify a start date and end date, then return each
    > date value within the specified range. Here's an example:
    >
    > Start Date : 6/1/06
    > End Date: 6/10/06
    >
    > Result set:
    > 6/1/06
    > 6/2/06
    > 6/3/06
    > .....
    > 6/10/06
    >
    > Can someone help me?
    >
    > Thanks,
    >
    > Joe
    >
    >
     
  4. Joe

    Joe
    Expand Collapse
    Guest

    Thanks Karl,

    I'll try that. One question, is there any way I can store all the
    logic in a function instead of having to create a temp table?

    It would look something like this : GetEachDay(StartDate,EndDate)

    I could call this function in a query and it would return every date in
    a new row.

    Is that possible?
     
  5. Eric Blitzer

    Eric Blitzer
    Expand Collapse
    Guest

    I am sure there is a better way but I already have a table whith every date
    in it for the next 5 years.(I created is using excel so it was quick and
    easy). I just run a query that groups by day and has the between criteria
    that is obtained from a form. The query can either be a select query or a
    make table query if you need to store the dates.

    "Joe" wrote:

    > Thanks Karl,
    >
    > I'll try that. One question, is there any way I can store all the
    > logic in a function instead of having to create a temp table?
    >
    > It would look something like this : GetEachDay(StartDate,EndDate)
    >
    > I could call this function in a query and it would return every date in
    > a new row.
    >
    > Is that possible?
    >
    >
     
  6. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    You should be able to have the function return a detached ADO recordset, but
    I don't see how you'd be able to use it for anything:

    Function GetEachDay(StartDate As Date, EndDate As Date) As ADODB.Recordset

    Dim rsCurr As ADODB.Recordset

    Set rsCurr = New ADODB.Recordset
    rsCurr.CursorLocation = adUseClient
    rsCurr.Fields.Append "CurrentDate", adDate
    rsCurr.Open

    Do While StartDate <= EndDate
    rsCurr.AddNew
    rsCurr!CurrentDate = StartDate
    rsCurr.Update
    StartDate = DateAdd("d", 1, StartDate)
    Loop

    Set GetEachDay = rsCurr

    End Function


    Sub CallIt()
    Dim rsReturned As ADODB.Recordset

    Set rsReturned = GetEachDay(#5/5/2006#, #5/10/2006#)
    rsReturned.MoveFirst
    Do Until rsReturned.EOF
    Debug.Print rsReturned!CurrentDate
    rsReturned.MoveNext
    Loop

    End Sub

    If I run CallIt in the Immediate Window, I get:

    2006/05/05
    2006/05/06
    2006/05/07
    2006/05/08
    2006/05/09
    2006/05/10



    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Joe" <josephsamuels@gmail.com> wrote in message
    news:1147289756.425064.127330@g10g2000cwb.googlegroups.com...
    > Thanks Karl,
    >
    > I'll try that. One question, is there any way I can store all the
    > logic in a function instead of having to create a temp table?
    >
    > It would look something like this : GetEachDay(StartDate,EndDate)
    >
    > I could call this function in a query and it would return every date in
    > a new row.
    >
    > Is that possible?
    >
     
  7. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    "Joe" <josephsamuels@gmail.com> wrote in message
    news:1147289756.425064.127330@g10g2000cwb.googlegroups.com...
    > Thanks Karl,
    >
    > I'll try that. One question, is there any way I can store all the
    > logic in a function instead of having to create a temp table?
    >
    > It would look something like this : GetEachDay(StartDate,EndDate)
    >
    > I could call this function in a query and it would return every date in
    > a new row.
    >
    > Is that possible?


    Help us understand. "... return every date in a new row..." in _what_ table
    or what other form? If you want the dates added to an existing table, you
    should be able to convert the Query that was suggested earlier to an Append
    Query. But you really have to explain what you want. Most of us come here to
    be of assistance, but not to play guessing games.

    Larry Linson
    Microsoft Access MVP
     
  8. Joe

    Joe
    Expand Collapse
    Guest

    Larry,

    I'll try to explain.

    I am trying to print a schedule report. I want to print every day
    within a user-specified date range, not just the dates where something
    is scheduled.

    My first step is to create an "eachday" query that will return each day
    between a user-specified start date and end date.

    I will then use an outer join to join the "eachday" query with the
    schedule table on the date field.

    Presumably this will give me a result set with every day within the
    specified range and a corresponding schedule record for that day, if
    one exists.

    A few months ago i did something similar. I had a developer friend of
    mine create a .udf for sql server 2000. This .udf would take two dates
    as parameters and return each day within that range. I could call that
    function in a view and it would return "each day in a new row" of the
    view's result set.

    In design mode, the column would look like this:
    GetEachDay(6/1/06,6/5/06)

    When i ran the view, the result set would look like this
    6/1/06
    6/2/06
    6/3/06
    6/4/06
    6/5/06

    Basically the function returned multiple values. I was also using SQL
    server then, not access. I don't know how to write a function that
    will return multiple values when called in an access query. I only
    know how to get a function to return one value at a time.

    Is that because SQL server .udf's are able to return multiple values
    and access functions aren't?

    I am currently using Karl's method of creating a temp table each time i
    run the report, but it's a little slow.

    Thanks.
     
  9. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    My method was not to create a Temp table. The CountNUM table is a permanent
    table. Just use the query in your "outer join to join the "eachday" query
    with the schedule table on the date field."

    "Joe" wrote:

    > Larry,
    >
    > I'll try to explain.
    >
    > I am trying to print a schedule report. I want to print every day
    > within a user-specified date range, not just the dates where something
    > is scheduled.
    >
    > My first step is to create an "eachday" query that will return each day
    > between a user-specified start date and end date.
    >
    > I will then use an outer join to join the "eachday" query with the
    > schedule table on the date field.
    >
    > Presumably this will give me a result set with every day within the
    > specified range and a corresponding schedule record for that day, if
    > one exists.
    >
    > A few months ago i did something similar. I had a developer friend of
    > mine create a .udf for sql server 2000. This .udf would take two dates
    > as parameters and return each day within that range. I could call that
    > function in a view and it would return "each day in a new row" of the
    > view's result set.
    >
    > In design mode, the column would look like this:
    > GetEachDay(6/1/06,6/5/06)
    >
    > When i ran the view, the result set would look like this
    > 6/1/06
    > 6/2/06
    > 6/3/06
    > 6/4/06
    > 6/5/06
    >
    > Basically the function returned multiple values. I was also using SQL
    > server then, not access. I don't know how to write a function that
    > will return multiple values when called in an access query. I only
    > know how to get a function to return one value at a time.
    >
    > Is that because SQL server .udf's are able to return multiple values
    > and access functions aren't?
    >
    > I am currently using Karl's method of creating a temp table each time i
    > run the report, but it's a little slow.
    >
    > Thanks.
    >
    >
     
  10. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    "Joe" <josephsamuels@gmail.com> wrote in message
    news:1147299135.009400.298990@i39g2000cwa.googlegroups.com...
    >
    > Is that because SQL server .udf's are able to return multiple values
    > and access functions aren't?


    I believe SQL Server UDFs are able to return recordsets.

    > I am currently using Karl's method of creating a temp table each time i
    > run the report, but it's a little slow.


    I don't believe Karl was suggesting that you create a temporary table each
    time. What's the maximum range you think you're ever going to need? Create a
    table that has twice as many rows as that, and keep it permanently in your
    table.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)
     
  11. Joe

    Joe
    Expand Collapse
    Guest

    Thanks Karl and Doug,

    Even though it bugs me to not be able to write code to solve this
    problem, I'll create a permanent table with a few years worth of dates.
    I appreciate your help.
     
  12. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    I think you're still misinterpretting Karl's advice. Nowhere did he suggest
    creating a table with dates in it.

    What he suggested was to create a table named CountNumber, with one Long
    Integer field CountNum in it. CountNum would contain numbers 1, 2, 3, 4, etc
    up as high as you like. (It may be simplest to create this in Excel and
    import it into your database)

    Then, he suggested to create a query:

    SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
    FROM CountNumber
    WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter end date])));

    Run that query, and you'll be prompted for Start and End dates. As long as
    the difference between the Start and End dates is not more days than you
    have rows in the CountNumber table, you'll get back 1 row for each day.


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Joe" <josephsamuels@gmail.com> wrote in message
    news:1147353720.638248.232710@j73g2000cwa.googlegroups.com...
    > Thanks Karl and Doug,
    >
    > Even though it bugs me to not be able to write code to solve this
    > problem, I'll create a permanent table with a few years worth of dates.
    > I appreciate your help.
    >
     

Share This Page