If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Return all dates within a range
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
|
|||
|
|||
Return all dates within a range
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 |
#3
|
|||
|
|||
Return all dates within a range
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? |
#4
|
|||
|
|||
Return all dates within a range
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? |
#5
|
|||
|
|||
Return all dates within a range
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" wrote in message 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) I could call this function in a query and it would return every date in a new row. Is that possible? |
#6
|
|||
|
|||
Return all dates within a range
"Joe" wrote in message 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) 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
|
|||
|
|||
Return all dates within a range
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. |
#8
|
|||
|
|||
Return all dates within a range
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. |
#9
|
|||
|
|||
Return all dates within a range
"Joe" wrote in message
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 table. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) |
#10
|
|||
|
|||
Return all dates within a range
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. |
|
Thread Tools | |
Display Modes | |
|
|