Return all dates within a range

#1 (**permalink**) 28-Jul-2006, 08:01 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9348-return-all-dates-within-a-range.html
End Date: 6/10/06

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

Can someone help me?

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
Thanks,

Joe

*

#2 (**permalink**) 28-Jul-2006, 08:01 AM

Create a table named CountNumber with a number field, Long Integer named
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
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
>
>

#3 (**permalink**) 28-Jul-2006, 08:01 AM

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?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348

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?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348

#4 (**permalink**) 28-Jul-2006, 08:01 AM

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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
>
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
> a new row.
>
> Is that possible?
>
>

#5 (**permalink**) 28-Jul-2006, 08:01 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348

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" wrote in message
news:1147289756.425064.127330@g10g2000cwb.googlegr oups.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)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
>
> I could call this function in a query and it would return every date in
> a new row.
>
> Is that possible?
>

#6 (**permalink**) 28-Jul-2006, 08:01 AM

"Joe" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
news:1147289756.425064.127330@g10g2000cwb.googlegr oups.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?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
>
> 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

#7 (**permalink**) 28-Jul-2006, 08:01 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
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.

#8 (**permalink**) 28-Jul-2006, 08:02 AM

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:

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
> 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 (**permalink**) 28-Jul-2006, 08:02 AM

"Joe" wrote in message
news:1147299135.009400.298990@i39g2000cwa.googlegr oups.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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
table.

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