Determining EVERY month between 2 given dates
in [Queries]
|
From: Bob Barrows on 18 May 2010 05:44 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"
From: Bob Barrows on 18 May 2010 06:05 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"
From: Edwinah63 on 18 May 2010 07:02
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 |