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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9348-return-all-dates-within-a-range.html
>
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9348
> 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.
>
>