A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Determining EVERY month between 2 given dates



 
 
Thread Tools Display Modes
  #11  
Old May 18th, 2010, 05:38 AM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default 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

  #12  
Old May 18th, 2010, 05:38 AM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default 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 AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1

  #13  
Old May 18th, 2010, 09:53 AM posted to microsoft.public.access.queries
Edwinah63
external usenet poster
 
Posts: 16
Default Determining EVERY month between 2 given dates

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





  #14  
Old May 18th, 2010, 09:56 AM posted to microsoft.public.access.queries
Edwinah63
external usenet poster
 
Posts: 16
Default Determining EVERY month between 2 given dates

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

:-)

  #15  
Old May 18th, 2010, 10:03 AM posted to microsoft.public.access.queries
Edwinah63
external usenet poster
 
Posts: 16
Default Determining EVERY month between 2 given dates

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.
  #16  
Old May 18th, 2010, 10:44 AM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Determining EVERY month between 2 given dates

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"


  #17  
Old May 18th, 2010, 11:05 AM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Determining EVERY month between 2 given dates

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"


  #18  
Old May 18th, 2010, 12:02 PM posted to microsoft.public.access.queries
Edwinah63
external usenet poster
 
Posts: 16
Default Determining EVERY month between 2 given dates

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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.