Return all dates within a range

[Joe]

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

 

[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]

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?

 

[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.

 

[Douglas J Steele]

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

 

[Larry Linson]

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

 

[Joe]

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.

 

[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."

 

[Douglas J. Steele]

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.

 

[Joe]

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.

 

[Douglas J Steele]

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.