"Rick Ratchford" <r...@amazingaccuracy.com> schrieb im Newsbeitrag news:dbfb2606d0c1448b930064474496a...@dolphin... > A while back, Igor gave me some help on pulling out mm/dd ranges (sets) from > my table. > > This is the code that does that. > > sSQL = "SELECT Date, Year, Month, Day, Open, High, Low, Close FROM [" & > gsTableName & "] " & _ > "WHERE ((Month - " & lngStartMth & ")*100 + (Day - " & > lngStartDay & ") + 1300) % 1300 <= ((" & _ > lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay - > lngStartDay & ") + 1300) % 1300 " & _ > "ORDER BY Year, ((Month - " & lngStartMth & ")*100 + (Day - " & > lngStartDay & ") + 1300) % 1300" > > The only problem I have is that there are some stray rows that are not > wanted.
Did you already tried the small function I've posted into the VB-database-group (which was based on Igors original suggestion)? This also shows, how to work with a CommandObject - (code is included again at the end of this post). It enhances the *Where* clause (not the Order By), to get rid of your "stray-rows" in the very first year of a "year-crossing-set"). Function GetMonthDayRange(TableName As String, _ ByVal MStart&, ByVal DStart&, _ ByVal MEnd&, ByVal DEnd&, _ ByVal InclYearSort As Boolean) As cRecordset Dim SQL As String 'a Cmd-SQL does *not* contain any direct "VarConcats", only @Placeholders SQL = "SELECT Date,Month,Day,Open,High,Low,Close FROM @TblName" & _ " WHERE (((mon...@mstart)*100 + (d...@dstart)+ 1300)% 1300" & _ " <=(( @me...@mstart)*100 +(@de...@dstart)+ 1300)% 1300)" & _ " AND (Year > (Select Min(Year) From @TblName) " & _ " OR (Month*100+Day >= @mstart*1...@dstart)) " 'enhance the above SQL about the appropriate, different Order By Clauses If InclYearSort Then SQL = SQL & "ORDER BY Year, Month, Day" Else SQL = SQL & "ORDER BY ((mon...@mstart)*100+(d...@dstart)+ 1300)% 1300" End If With Cnn.CreateSelectCommand(SQL) 'create a Select-Command-Object 'now we replace the @PlaceHolders with the current Var-Content, 'which we've got passed in our Function-Parameters .ReplColumnOrTableName !TblName, TableName .SetInt32 !MStart, MStart .SetInt32 !DStart, DStart .SetInt32 !MEnd, MEnd .SetInt32 !DEnd, DEnd Set GetMonthDayRange = .Execute 'the Cmd.Execute returns a Recordset End With End Function Olaf _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users