I need to look up an item price. The price file is updated as of the close of business the day before (and I now have a large price file recordset). So, what I do is open the price file, and move to the end.
I want to price the items for the past five days… so, I need to update my mysql search to adjust the date variable to the last date minus one day. in Visual basic it was simply a matter of tempdate = date - 1, but this does not work in Xojo.
Is there a simple function that does this? Parsedate does not seem to provide the mechanism that I need?
You’d use the date class.
Dim d As New Date ’ Will be now
d.day = d.day - 1
with this you can add WHERE pricedate = ‘" + d.sqldate + "’;" to your sql query or better use a prepared statement.
Ok, that solves half of my problem… I know have one operator for the comparison, d.day. Mysql query returns the latest date available in the recordset. call it recorddate; it is returned, of course, as string value.
SO, I need to convert recorddate to date to compare it to d.day… if then I want to step backwards by one day for five days… so, my next sql query would be “select * from pricefile where recorddate = '”+ recorddate - 1 + “’”
Does that make sense? I guess my question should be better worded: I want to retrieve the last date of pricing available, then step back and retrieve a price one day at a time for five days. SO, I guess I need to convert the mysql string value to a date value, then use the method you show above to decrease it by one day, and generate a new sql query based upon that modified date… does that make sense?
You should be able to use:
your_date_variable = rs.Field(“recorddate”).DateValue
Oh… thanks Johnny… that makes it much simpilier. I did not see that option in the mysql section of the handbook… I have a hard time finding the examples / keywords I need in that document.