Determining EVERY month between 2 given dates [Queries]

Prev: Flag a value in a group...
Next: Query result shows all fields

From: PieterLinden via AccessMonster.com on 18 May 2010 00:38

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

From: PieterLinden via AccessMonster.com on 18 May 2010 00:38

From: Edwinah63 on 18 May 2010 04:53

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 :-)

From: Edwinah63 on 18 May 2010 04:56

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#));

:-)

From: Edwinah63 on 18 May 2010 05:03

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.

First | Prev | Next | Last
Pages: 1 2 3 4
Prev: Flag a value in a group...
Next: Query result shows all fields