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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|