Hi everyone, I have a table with the following data upon which I would
like to report:

SomeDt        SomeValue
1-sep-09             100
12-dec-09           200
02-feb10             50
14-apr-10            75

I need to report on this data showing values for all months within the
given min and max dates on the file so:
These dates need to be part of a *rolling* report.

I have data for Sep, Dec,Feb and Apr, but I want to display on the
report ALL months regardless of whether they have data in the table
and return 0 where they do not. Thus:

Month           Value
Sep 09            100
Oct 09            0
Nov 09            0
Dec 09            200
Jan 10            0
Feb 10            50
Mar 10           0
Apr 10            75


I can get the min and max and the count of months easily enough:

select min(SomeDt) as StDt, max(SomeDt) as EndDt, datediff("m" , StDt,
EndDt) as CountofMonthsBetween from SomeTable

What I cannot get is how to then determine what the months are in
between. What I want if possible is a resultset that looks something
like this

StDt            EndDt         Mth
01/9/09        14/4/10       Sep 09
01/9/09        14/4/10       Oct 09
01/9/09        14/4/10       Nov 09
01/9/09        14/4/10       Dec 09
01/9/09        14/4/10       Jan 10
01/9/09        14/4/10       Feb 10
01/9/09        14/4/10       Mar 10
01/9/09        14/4/10       Apr 10

How can I do it?

As much as possible I want to use QUERY resultsets, rather than
creating intermediary tables etc which are just messy in MS Access,
but will use them if no other choice.

Happy to write a function if needs be to calc the Mth column. Am
absolutely desperate so all help greatly appreciated.  If it can't be
done at all via queries alone, please let me know this too.

Edwina63

The missing months have to come from somewhere, so you need a table to 
generate them.

1. Create a new table with just one field named (say) CountID, type Number, 
and mark it as primary key. Save the table as (say) tblCount.

2. Enter records into this table, from zero to the largest number of months 
you will need for your report. If there could be a large number of months, 
here's some code to enter the records for you:
    http://allenbrowne.com/ser-39.html

3. Create a query using tblCount as the source table.
Drag CountID into the grid. In the Criteria row under this field, enter:
    < [How many months?]

4. Type this expression into the next column, the Field row:
    TheMonth: DateAdd("m", [CountID], [StartMonth])

5. Declare the Parameters (Parameters on ribbon or Query menu.)
Access opens the Parameters dialog.
Enter 2 rows, using exactly the same names as you used above:
    [How many months?]    Long
    StartMonth                  Date/Time

6. Test. This should generate a record for every month.
Save the query as qryCount.

7. Create a query using your existing table.
Enter this expression into the Field row:
    MonthStart: [SomeDt] - Day([SomeDt]) + 1

8. Depress the Total button on the toolbar/ribbon.
Access adds a Total row to the design grid.
Accept Group By under this field.

9. Drag SomeValue into the grid.
In the Total row under this field, choose Sum.
Test: the query will give you one total for each month that has data.
Save the query as qryMonthData.

10. Create another query using qryCount and qryMonthData as input 'tables'.

11. Drag qryCount.TheMonth onto qryMonthData.MonthStart.
Access draws a line between the two tables.

12. Double-click the line between the 2 dialogs.
Access opens a dialog with 3 options.
Choose the one that says:
    All records from qryCount, and any matches from qryMonthData.

(Technically, that's known as an outer join, and it's what gives you every 
month.)

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Edwinah63" <edwinah@customercare.com.au> wrote in message 
news:592bf063-f748-40d6-9b87-5270e4f575f6@42g2000prb.googlegroups.com...
> Hi everyone, I have a table with the following data upon which I would
> like to report:
>
> SomeDt        SomeValue
> 1-sep-09             100
> 12-dec-09           200
> 02-feb10             50
> 14-apr-10            75
>
> I need to report on this data showing values for all months within the
> given min and max dates on the file so:
> These dates need to be part of a *rolling* report.
>
> I have data for Sep, Dec,Feb and Apr, but I want to display on the
> report ALL months regardless of whether they have data in the table
> and return 0 where they do not. Thus:
>
> Month           Value
> Sep 09            100
> Oct 09            0
> Nov 09            0
> Dec 09            200
> Jan 10            0
> Feb 10            50
> Mar 10           0
> Apr 10            75
>
>
> I can get the min and max and the count of months easily enough:
>
> select min(SomeDt) as StDt, max(SomeDt) as EndDt, datediff("m" , StDt,
> EndDt) as CountofMonthsBetween from SomeTable
>
> What I cannot get is how to then determine what the months are in
> between. What I want if possible is a resultset that looks something
> like this
>
> StDt            EndDt         Mth
> 01/9/09        14/4/10       Sep 09
> 01/9/09        14/4/10       Oct 09
> 01/9/09        14/4/10       Nov 09
> 01/9/09        14/4/10       Dec 09
> 01/9/09        14/4/10       Jan 10
> 01/9/09        14/4/10       Feb 10
> 01/9/09        14/4/10       Mar 10
> 01/9/09        14/4/10       Apr 10
>
> How can I do it?
>
> As much as possible I want to use QUERY resultsets, rather than
> creating intermediary tables etc which are just messy in MS Access,
> but will use them if no other choice.
>
> Happy to write a function if needs be to calc the Mth column. Am
> absolutely desperate so all help greatly appreciated.  If it can't be
> done at all via queries alone, please let me know this too.
>
> Edwina63
>
>
>
>
>
> 

Hi Allen,

Thanks for your solution, it is very neat.  Now let me ask the truly
stoopid question: is it possible to generate those eight records that
are put into tblCount via a query alone ie: based on the the record
count (in this example 8 months between Sep and Apr), is it possible
to force a *query* to return 8  records (containing 1 to 8, 0 to 7 or
whatever)?

I am certain the answer is no, but I am interested to have that
confirmed by someone who knows Access well (or queries in general and
likes a challenge!).  As a quick background, I am used to doing these
sorts of things in MS SQL stored procs. I would create the kind of
table you suggested as a temp table within the procedure and outer
join that table back to my query containing the data all as you have
outlined.  The advantage of stored procs is that when they go out of
scope, all the temp tables I have created are destroyed with them.

I am worried about creating the table because once I have the final
resultset I want all the intermediary steps destroyed. This is coupled
with the fact that the owner of this database wants it to be as
codeless as possible.  Hence the idea of forcing a query to return 8
records in a resultset (which seems to be the nearest thing to a temp
table I can find in Access).

Any suggestions as the best way to approach this?

Edwinah63

ps not sure how i would go about forcing a query to return 8 empty
records in ms sql either!! :-) so over to the floor!

>ps not sure how i would go about forcing a query to return 8 empty
>records in ms sql either!! :-) so over to the floor!

Bite the bullet.  Create the tblDates table and then use it in your query.
Then you'll create a deliberate partial cartesian product, constraining the
dates to be between a start and end date.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1

Do you already have a table in your database that contains an autonumber 
field? If so, use that instead of creating a new table.
Otherwise, a dynamic solution is not possible. You can of course hard-code 
some unioned queries to force the missing records to be included.

As an alternative, create a new database in code, create the Numbers table 
in it, link to it, and use it in your query, deleting the database when 
finished. I'm sure Allen has an example of VBA code to create a database on 
his website. If not, try www.rogersaccesslibrary.com

As for SQL Server, given the existence of a Numbers table, the exact same 
solution is possible without the overhead of temp tables.

Edwinah63 wrote:
> Hi Allen,
>
> Thanks for your solution, it is very neat.  Now let me ask the truly
> stoopid question: is it possible to generate those eight records that
> are put into tblCount via a query alone ie: based on the the record
> count (in this example 8 months between Sep and Apr), is it possible
> to force a *query* to return 8  records (containing 1 to 8, 0 to 7 or
> whatever)?
>
> I am certain the answer is no, but I am interested to have that
> confirmed by someone who knows Access well (or queries in general and
> likes a challenge!).  As a quick background, I am used to doing these
> sorts of things in MS SQL stored procs. I would create the kind of
> table you suggested as a temp table within the procedure and outer
> join that table back to my query containing the data all as you have
> outlined.  The advantage of stored procs is that when they go out of
> scope, all the temp tables I have created are destroyed with them.
>
> I am worried about creating the table because once I have the final
> resultset I want all the intermediary steps destroyed. This is coupled
> with the fact that the owner of this database wants it to be as
> codeless as possible.  Hence the idea of forcing a query to return 8
> records in a resultset (which seems to be the nearest thing to a temp
> table I can find in Access).
>
> Any suggestions as the best way to approach this?
>
> Edwinah63
>
> ps not sure how i would go about forcing a query to return 8 empty
> records in ms sql either!! :-) so over to the floor!

-- 
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" 

FWIW,
here's Tony's explanation of how to use Temp Tables in Access....
http://www.granite.ab.ca/access/temptables.htm

not quite the same as SQL Server, but the best you're going to do in Access...


-- 
Message posted via http://www.accessmonster.com

>>You can of course hard-code
>>some unioned queries to force the missing records to be included.

D'oh! I completely forgot about creating a dynamic union query! Thank
you for reminding me!

Maybe something like this?

public sub CreateAQuery(mthsBetween as integer)

dim i as integer
dim sql as string

for i = 0 to mthsBetween

sql = sql & "select " & i & " as Mth union "

next i

<------Can I do this next bit??--->

Currentdb.Execute " If exists(SELECT name FROM sysobjects WHERE name =
'MyUnionQuery' DROP QUERY MyUnionQuery;"
CurrentDb.Execute "CREATE QUERY MyUnionQuery AS " & sql


end sub

The code above isn't quite right but you get the idea.

Had a hunt around the internet for a "Create Query" statement. Is
there one? Would prefer to stick to SQL statements wherever possible
but will use querydefs etc otherwise.

A big thank you to everyone who responded :-)

Edwinah63 wrote:
>>> You can of course hard-code
>>> some unioned queries to force the missing records to be included.
>
> D'oh! I completely forgot about creating a dynamic union query! Thank
> you for reminding me!
>
> Maybe something like this?
>
> public sub CreateAQuery(mthsBetween as integer)
>
> dim i as integer
> dim sql as string
>
> for i = 0 to mthsBetween
>
> sql = sql & "select " & i & " as Mth union "
>
> next i
>
> <------Can I do this next bit??--->
>
> Currentdb.Execute " If exists(SELECT name FROM sysobjects WHERE name =
> 'MyUnionQuery' DROP QUERY MyUnionQuery;"
> CurrentDb.Execute "CREATE QUERY MyUnionQuery AS " & sql
>
>
> end sub
>
> The code above isn't quite right but you get the idea.
>
> Had a hunt around the internet for a "Create Query" statement. Is
> there one? Would prefer to stick to SQL statements wherever possible
> but will use querydefs etc otherwise.
>
> A big thank you to everyone who responded :-)

Aside from the test for existence: absolutely. In this case, it would be

CREATE VIEW view [(field1[, field2[, ...]])] AS selectstatement

 If you parameterize it or create an action query, it would be

CREATE PROCEDURE procedure
    [param1 datatype[, param2 datatype[, ...]] AS sqlstatement

Online help has a well-hidden section on JetSQL, but I did manage to find it 
:-)
Just hit F1, find the again well-hidden link on the help screen to get to 
the table of contents, and  find the node called "Microsoft Jet SQL 
Reference" - the last bit isn't so hard :-)

You would have to look at the querydefs collection (if using DAO) or the 
ADOX Views or Procedures collection (if using ADO) to find out if the query 
exists. But you know ... since you're already into the Querydefs collection, 
you might as well simply use a querydef object ... it will truly be 
temporary if you omit the qryname argument from the CreateQuerydef 
statement. Since you are using VBA, you're code won't be portable anyways, 
so you might as well do it the easy way.

Oh! And don't forget: unlike Transact-SQL, Jet SQL requires a FROM clause 
with table expression. You don't have to actually retrieve any data from the 
table, but you do have to name one.

Transact-SQl:
Select 'a' as col1, 2 as col2
union
Select 'b', 4

Jet SQL:
Select TOP 1 'a' as col1, 2 as col2 FROM existingtable

union
Select TOP 1 'b', 4 FROM existingtable

-- 
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" 

Thanks for your reply. I am having trouble now with the Create View
Statement.

To check I had the right syntax I created a dummy table with 1 field,
1 record. I then created the sample statement in the SQL view of both
an ordinary and so called "Data Definition" query

create view MyView as select dummyid from tblDummy

Both come back with the error "Syntax error in Create Table statement"

What am I doing wrong?

Had a google around and it seems that Access 2007 will not recognize
create view statement for this database unless the ANSI92
compatibility is turned on and the ANSI92 option under Access Options/
Object Designers/Query Design/Sql Server Compatible Syntax is greyed
out.

Database is an Access 2007 database looking at Access 2007 tables
within the same database.  I really want to avoid the whole querydefs
thing if I can.

Are you sure that JetSQL supports the DDL statements Create View/
Procedure?  If so may I have the full steps please?

Edwinah63 wrote:
> Thanks for your reply. I am having trouble now with the Create View
> Statement.
>
> To check I had the right syntax I created a dummy table with 1 field,
> 1 record. I then created the sample statement in the SQL view of both
> an ordinary and so called "Data Definition" query
>
> create view MyView as select dummyid from tblDummy
>
> Both come back with the error "Syntax error in Create Table statement"
>
> What am I doing wrong?
>
> Had a google around and it seems that Access 2007 will not recognize
> create view statement for this database unless the ANSI92
> compatibility is turned on and the ANSI92 option under Access Options/
> Object Designers/Query Design/Sql Server Compatible Syntax is greyed
> out.
>
> Database is an Access 2007 database looking at Access 2007 tables
> within the same database.  I really want to avoid the whole querydefs
> thing if I can.
>
> Are you sure that JetSQL supports the DDL statements Create View/
> Procedure?  If so may I have the full steps please?

I can't give you any more than what is in the online help .. sorry.
In A2003, I did have to check the ANSI92 option for the database I was 
testing with in order to make the CREATE VIEW statement work in the SQL View 
of a query builder window. Without that option turned on, I got the same 
error you did.

This snippet of code also failed until I turned on ANSI92:

Sub testcreateview()
Dim db As DAO.Database
Set db = CurrentDb
db.Execute "CREATE VIEW testqry   AS select * from table1", dbFailOnError
End Sub

If you cannot turn on that option, it appears you will have to resort to the 
querydef (or ADOX Views) method. I'm baffled as to why you are so desperate 
to avoid it. Is it because of the need to create a Reference to DAO? If so, 
you can do that in code by using the Application object's References 
collection, which has two methods for creating references: AddFromFile and 
AddFromGuid. Here is an example of the latter:

Dim ref As Reference, DAOfound As Boolean
DAOfound = False
For Each ref In Application.References
  If ref.Name = "DAO" Then
    DAOfound = True
    exit for
  End If
Next ref
If Not DAOfound Then
  Application.References.AddFromGuid 
"{00025E01-0000-0000-C000-000000000046}", 3, 6
End If


They are very simple to use. You don't even have to drop the querydef if 
you've already created it - just set the .SQL property to the new sql 
statement, like this:

sub createqry()
dim db as database,qdf as querydef,strsql as string
'build your string
set db=currentdb
on error resume next
set qdf=db.querydefs("qryname")
if err <> 0 then set qdf=db.createquerydef("qryname")
qdf.sql=strsql
end sub

-- 
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" 

Edwina,
you don't need to save the query at all... you just have to _execute_ it.
You could do something like this...
1. create a function to create your dynamic union query STRING (there's no
need to save it!)
2. If you keep a dummy query around in your queries, you can just overwrite
its SQL like this:

DBEngine(0)(0).QueryDefs("MyUnionQuery").SQL = fCreateUnionSQL()

as long as fCreateUnionSQL() returns a valid SQL statement, everything should
work fine.

Pieter



Edwinah63 wrote:
>>>You can of course hard-code
>>>some unioned queries to force the missing records to be included.
>
>D'oh! I completely forgot about creating a dynamic union query! Thank
>you for reminding me!
>
>Maybe something like this?
>
>public sub CreateAQuery(mthsBetween as integer)
>
>dim i as integer
>dim sql as string
>
>for i = 0 to mthsBetween
>
>sql = sql & "select " & i & " as Mth union "
>
>next i
>
><------Can I do this next bit??--->
>
>Currentdb.Execute " If exists(SELECT name FROM sysobjects WHERE name =
>'MyUnionQuery' DROP QUERY MyUnionQuery;"
>CurrentDb.Execute "CREATE QUERY MyUnionQuery AS " & sql
>
>end sub
>
>The code above isn't quite right but you get the idea.
>
>Had a hunt around the internet for a "Create Query" statement. Is
>there one? Would prefer to stick to SQL statements wherever possible
>but will use querydefs etc otherwise.
>
>A big thank you to everyone who responded :-)

-- 
Message posted via http://www.accessmonster.com

Edwina,
you don't need to save the query at all... you just have to _execute_ it.
You could do something like this...
1. create a function to create your dynamic union query STRING (there's no
need to save it!)
2. If you keep a dummy query around in your queries, you can just overwrite
its SQL like this:

DBEngine(0)(0).QueryDefs("MyUnionQuery").SQL = fCreateUnionSQL()

as long as fCreateUnionSQL() returns a valid SQL statement, everything should
work fine.

Pieter



Edwinah63 wrote:
>>>You can of course hard-code
>>>some unioned queries to force the missing records to be included.
>
>D'oh! I completely forgot about creating a dynamic union query! Thank
>you for reminding me!
>
>Maybe something like this?
>
>public sub CreateAQuery(mthsBetween as integer)
>
>dim i as integer
>dim sql as string
>
>for i = 0 to mthsBetween
>
>sql = sql & "select " & i & " as Mth union "
>
>next i
>
><------Can I do this next bit??--->
>
>Currentdb.Execute " If exists(SELECT name FROM sysobjects WHERE name =
>'MyUnionQuery' DROP QUERY MyUnionQuery;"
>CurrentDb.Execute "CREATE QUERY MyUnionQuery AS " & sql
>
>end sub
>
>The code above isn't quite right but you get the idea.
>
>Had a hunt around the internet for a "Create Query" statement. Is
>there one? Would prefer to stick to SQL statements wherever possible
>but will use querydefs etc otherwise.
>
>A big thank you to everyone who responded :-)

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1

Hi to Bob and Pieter (and everyone else),

Thanks for all your help and patience!  I went with creating the union
query since I need to outer join this back to get the results I want.

Here is the final code - for posterity
This is sample code so the functions and variables probably don't have
such good names. I am sure there are more elegant ways of achieving
this code, but it will do for me - it works!!.


Public Function MonthsBetweenDates(StDt As Date, MaxMths As Integer)
As Integer 'works
Dim sql As String
On Error Resume Next
CurrentDb.QueryDefs.Delete "MyUnion"

On Error GoTo err
sql = getMths(StDt, MaxMths)
CurrentDb.CreateQueryDef "MyUnion", sql
CurrentDb.QueryDefs.Refresh

Exit Function
err:
    MsgBox err.Description
End Function


Public Function getMths(RangeStDt As Date, MaxMths As Integer) As
String 'works
On Error GoTo err
Dim i As Integer
Dim sql As String
Dim MthStDt As Date
Dim MthEndDt As Date
Dim RangeEndDt As Date

For i = 0 To MaxMths

    'US_Date function used since Access for reasons known only to
itself converts 01/11/2011 to 11/1/2011 and vice versa, despite region
set for Oz

    RangeEndDt = DateAdd("m", 12, RangeStDt) - 1
    MthStDt = DateAdd("m", i, RangeStDt)
    MthEndDt = DateAdd("m", i + 1, RangeStDt) - 1

    sql = sql & "select " & US_Date(RangeStDt) & " as RangeStDt, "
    sql = sql & "#" & RangeEndDt & "# as RangeEndDt, "
    sql = sql & US_Date(MthStDt) & " as MthStDt, "
    sql = sql & "#" & MthEndDt & "# as MthEndDt "
    sql = sql & "from tblDummy union "

Next i

    'tidy up query remove final union clause
    sql = Left(sql, Len(sql) - Len(" union "))

    getMths = sql

Exit Function
err:
    MsgBox err.Description
End Function

Union query for 8 months looks like this:

select #6/1/2011# as RangeStDt, #31/05/2012# as RangeEndDt, #6/1/2011#
as MthStDt, #30/06/2011# as MthEndDt from tblDummy union
select #6/1/2011# as RangeStDt, #31/05/2012# as RangeEndDt, #7/1/2011#
as MthStDt, #31/07/2011# as MthEndDt from tblDummy union
select #6/1/2011# as RangeStDt, #31/05/2012# as RangeEndDt, #8/1/2011#
as MthStDt, #31/08/2011# as MthEndDt from tblDummy union
select #6/1/2011# as RangeStDt, #31/05/2012# as RangeEndDt, #9/1/2011#
as MthStDt, #30/09/2011# as MthEndDt from tblDummy union
select #6/1/2011# as RangeStDt, #31/05/2012# as RangeEndDt,
#10/1/2011# as MthStDt, #31/10/2011# as MthEndDt from tblDummy union
select #6/1/2011# as RangeStDt, #31/05/2012# as RangeEndDt,
#11/1/2011# as MthStDt, #30/11/2011# as MthEndDt from tblDummy union
select #6/1/2011# as RangeStDt, #31/05/2012# as RangeEndDt,
#12/1/2011# as MthStDt, #31/12/2011# as MthEndDt from tblDummy union
select #6/1/2011# as RangeStDt, #31/05/2012# as RangeEndDt, #1/1/2012#
as MthStDt, #31/01/2012# as MthEndDt from tblDummy UNION
select #6/1/2011# as RangeStDt, #31/05/2012# as RangeEndDt, #2/1/2012#
as MthStDt, #29/02/2012# as MthEndDt from tblDummy;

There is another query just to get the sample recordset from the table
containing the production data, then I outer join it back thus so:

SELECT CDate("01/" & Month([dt]) & "/" & Year([dt])) AS MthStDt,
Table1.name, Table1.number AS Qty, Table1.dt
FROM Table1
WHERE (((Table1.dt) Between #6/1/2011# And #2/28/2012#));

Put it all together:

SELECT
MyUnion.RangeStDt,
MyUnion.RangeEndDt,
MyUnion.MthStDt,
MyUnion.MthEndDt,
GraphSample01.name,
IIf(IsNull([qty]),0,[qty]) AS Qtyx
FROM MyUnion LEFT JOIN GraphSample01 ON MyUnion.MthStDt =
GraphSample01.MthStDt;

Hopefully this code can be a starting point for someone else in the
same situation.

Again, thank you thank you thank you to everyone who helped out with
this :-)

D'oh! forgot to add the very first query that needs to be run:

SELECT Min([dt]) AS MinDt, Max([dt]) AS MaxDt, DateDiff("m",[mindt],
[maxdt]) AS MaxMths, MonthsBetweenDates([MinDt],[Maxmths]) AS MthsBtwn
FROM Table1
WHERE (((Table1.dt)>#5/1/2011#));

:-)

Now I think about it, just dumping the data into a table (which I
dislike because they can contain stale data if not managed) and right
joining back would have been easier and a lot less code intensive and
I'm still stuck with a potentially stale query if it is not managed :(

A lot of work for something that can be achieved so easily in a stored
proc.

Edwinah63 wrote:
> Hi to Bob and Pieter (and everyone else),
>
> Thanks for all your help and patience!  I went with creating the union
> query since I need to outer join this back to get the results I want.
>
> Here is the final code - for posterity
> This is sample code so the functions and variables probably don't have
> such good names. I am sure there are more elegant ways of achieving
> this code, but it will do for me - it works!!.

A couple of comments if you don't mind. I'm sure you're very proud of 
accomplishing this goal (with good reason), but theres are several problems 
with this code that make it a poor example for beginners, several poor 
programming practices that should not be perpetuated.
See inline.
>
>
> Public Function MonthsBetweenDates(StDt As Date, MaxMths As Integer)
> As Integer 'works
> Dim sql As String
> On Error Resume Next
> CurrentDb.QueryDefs.Delete "MyUnion"

I'm really baffled by your commitment to this modus operandi of first 
dropping the querydef and then recreating it. It's not even something that 
needs to be done in SQL Server given the "ALTER ..." commands. I won't 
bother posting the code to do this again.

>
> On Error GoTo err

"err" is the name of  a builtin VBA object and should therefore be avoided. 
You seem to have gotten away with it here but you may not be so fortunate in 
the future. Avoid using reserved keywords for your own code. "err_handler" 
is a much better name for your error handler.

> sql = getMths(StDt, MaxMths)
> CurrentDb.CreateQueryDef "MyUnion", sql

It's a bad idea to make multiple calls to the CurrentDb function (yes, it's 
a function). You should drop this habit now - it's a performance drain. 
Instead, declare a Database variable (as I showed in my samples) and assign 
the result of CurrentDb to it. Even better would be the technique 
illustrated by Peter of using DBEngine(0)(0)

> CurrentDb.QueryDefs.Refresh

This call to Refresh is another performance drain that is usually not 
necessary.


>
> Exit Function
> err:
>    MsgBox err.Description
This is typically followed by GoTo err_handler so that your function has a 
single exit point


> End Function
>
>
> Public Function getMths(RangeStDt As Date, MaxMths As Integer) As
> String 'works
> On Error GoTo err
> Dim i As Integer
> Dim sql As String
> Dim MthStDt As Date
> Dim MthEndDt As Date
> Dim RangeEndDt As Date
>
> For i = 0 To MaxMths
>
>    'US_Date function used since Access for reasons known only to
> itself converts 01/11/2011 to 11/1/2011 and vice versa, despite region
> set for Oz

 See online help. Date literals must use either US date format or better, 
the less ambiguous IS format: yyyy-mm-dd. Anyways, a custom function is 
unnecessary - you can use the builtin Format fuction - see below:
>
<snip>

> Put it all together:
>
> SELECT
> MyUnion.RangeStDt,
> MyUnion.RangeEndDt,
> MyUnion.MthStDt,
> MyUnion.MthEndDt,
> GraphSample01.name,
> IIf(IsNull([qty]),0,[qty]) AS Qtyx
> FROM MyUnion LEFT JOIN GraphSample01 ON MyUnion.MthStDt =
> GraphSample01.MthStDt;
>


-- 
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" 

Edwinah63 wrote:
> Now I think about it, just dumping the data into a table (which I
> dislike because they can contain stale data if not managed) and right
> joining back would have been easier and a lot less code intensive and
> I'm still stuck with a potentially stale query if it is not managed :(
>
And you've also violated your goal of not creating "extra" tables in the 
database.:-)

The original solution offered by Allen is not only more suitable for this 
situation, it also provides a tool that can help solve other problems you 
might run into in the future. This is not the only situation where a Numbers 
table can come in handy. There are even situations in SQL Server where a 
Numbers table can help provide set-based solutions to problems (avoiding 
cursors), although the introduction of CTEs has made it less necessary to 
have a permanent table. String-parsing is certainly one of the places where 
a Numbers table is useful.

> A lot of work for something that can be achieved so easily in a stored
> proc.

All right, this is at least the third time you've expressed this type of 
sentiment. Please, stop moaning about the tool you're using and learn to 
live and work within its limitations. Jet is a file-based rdbms and was 
never intended to offer the functionality of a server-database like SQL 
Server.

-- 
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" 

Hi Bob,

Thanks for your reply. I did have fun along the way and will take your
comments on board and go with Allen's solution - but one must try
these things :)

E