Determining EVERY month between 2 given dates

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