Hi Igor- Unfortunately, I have to take the examples provided and convert them into STRINGS that Visual Basic 6 can use.
Here is that string from your earlier example: sSQL = "SELECT Date, Month, Day, Open, High, Low, Close FROM [" & gsTableName & "] " & "WHERE ((Month - " & lngStartMth & ")*100 + (Day - " & lngStartDay & ") + 1300) % 1300 <= " & lngEndMth - lngStartMth & " * 100 + " & ((lngEndDay - lngStartDay) + 1300) Mod 1300 & " ORDER BY ((Month - " & lngStartMth & ")*100 + (Day - " & lngStartDay & ") + 1300) % 1300" The application gets from the User the Starting MM and DD and Ending MM and DD. Starting month is stored in variable lngStartMth (long value). Starting day is stored in lngStartDay. Ending month in lngEndMth and Ending day in lngEndDay. Strings in VB6 are encased in double quotes. "String". And so when I have to insert my long variables shown above, I have to append the SQL string around the variables using the append &. I mention these things, not that you care about VB6 because that isn't your language. I mention it because perhaps you can spot where I did not correctly formulate based on your example. When I run this SQL statement using April 1 to August 1 (start 04/01 end 08/01), it works wonderfully. However, turn this around as 08/01 to 04/01, and it returns no records at all. I had the program print out both statements where the variables have already been resolved. Here are both for investigation. WORKS ===== SELECT Date, Month, Day, Open, High, Low, Close FROM [AUS $, COMP-R AN_REV] WHERE ((Month - 4)*100 + (Day - 1) + 1300) % 1300 <= 4 * 100 + 0 ORDER BY ((Month - 4)*100 + (Day - 1) + 1300) % 1300 DOES NOT WORK ============== SELECT Date, Month, Day, Open, High, Low, Close FROM [AUS $, COMP-R AN_REV] WHERE ((Month - 8)*100 + (Day - 1) + 1300) % 1300 <= -4 * 100 + 0 ORDER BY ((Month - 8)*100 + (Day - 1) + 1300) % 1300 Does anything in the second statement jump out as the issue? Thanks. :) Rick #>-----Original Message----- #>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- #>boun...@sqlite.org] On Behalf Of Igor Tandetnik #>Sent: Monday, October 12, 2009 6:53 AM #>To: sqlite-users@sqlite.org #>Subject: Re: [sqlite] Need Help SQL #> #>Rick Ratchford wrote: #>> #>There is a slight complication with the case where endDate < #>> startDate, #>where you want to wrap around to next year. For that, I #>> simply need to #>perform calculations modulo a large number - any #>> number greater than the #>representation of 12/31 in my scheme. 1300 #>> is one such number. #> #>> #>Igor Tandetnik #>> #>> That slight complication is still there though. It works when the #>> start < end, but it fails the other way around. #> #>Precisely how does it fail? Seems to work for me: #> #>create table t(month integer, day integer, price integer); #>insert into t values(1, 10, 1); #>insert into t values(5, 10, 2); #>insert into t values(12, 10, 3); #> #>-- select rows between 10/1 and 3/31 #>select Month, Day, Price #>from t, (select 10 as StartMonth, 1 as StartDay, 3 as EndMonth, 31 as #>EndDay) #>where ((Month - StartMonth)*100 + (Day - StartDay) + 1300) % 1300 < #> ((EndMonth - StartMonth)*100 + (EndDay - StartDay) + 1300) % #>1300 #>order by ((Month - StartMonth)*100 + (Day - StartDay) + 1300) % 1300; #>12|10|3 #>1|10|1 #> #>Isn't that what you wanted? #> #>Igor Tandetnik #> #> #>_______________________________________________ #>sqlite-users mailing list #>sqlite-users@sqlite.org #>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
