#>> 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"
#>
#>In the the right-hand side of <= operator, you only apply "mod 1300" to
#>((lngEndDay - lngStartDay) + 1300) part but not to
#>(lngEndMth - lngStartMth)*100 part. Be careful which calculations you
#>perform in the host language, and which you embed in SQL
#>statement. You have a strange mix of the two.
#>
#>Doesn't your VB SQLite binding support parameterized statements? If so,
#>I'd suggest you dump it and find a better one.
#>
#>Igor Tandetnik
The <= replaced the < you had in the original because it would return UP TO
but not including the Ending mm/dd.
Since the problem starts following the WHERE clause, I'm not going to
include the info before it.
I have in my code...
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"
Your code:
WHERE ((Month - :StartMonth) * 100 + (Day - :StartDay) + 1300) % 1300 <
VB:
WHERE ((Month - " & lngStartMth & ") * 100 + (Day - " & lngStartDay & ") +
1300 % 1300 <=
All that was done is that :StartMonth is replaced with variable lngStartMth
and :StartDay replaced with variable lngStartDay. Both long values.
Your code:
((:EndMonth - :StartMonth) * 100 + (:EndDay - :StartDay) + 1300) % 1300
VB:
(" & lngEndMth - lngStartMth) & ") * 100 + " & ((lngEndDay - lngStartDay) +
1300) Mod 1300
In the VB version, I'm simply doing all the math outside the string itself
and concat it within the string. Just for giggles, I changed so the math is
done inside the SQL string and changed mod back to %. Same results.
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
#>In the the right-hand side of <= operator, you only apply "mod 1300" to
#>((lngEndDay - lngStartDay) + 1300) part but not to
#>(lngEndMth - lngStartMth)*100 part.
Well, after going through all the above steps explaining what I did to
convert, it appears that I can SEE what you mean by the above statement.
Rather than this...
(" & lngEndMth - lngStartMth & ") * 100 + ((" & lngEndDay - lngStartDay & ")
+ 1300) % 1300 "
It should have been this...
((" & lngEndMth - lngStartMth & ") * 100 + (" & lngEndDay - lngStartDay & ")
+ 1300) % 1300 "
I'm HAPPY to say that it WORKS!!!
Thanks for your help and time.
Cheers!
Rick
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
