By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,852 Members | 2,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,852 IT Pros & Developers. It's quick & easy.

FIND LAST instance of 'quality' data

P: 98
I have a table TEMPSYMBOL with a variable number of stock symbols in it. I need to use this table to filter a very large table called DAILYPRICE for the last instance of decent data 'MARKETPRICE' from a table called DAILYPRICE. I thought I would create a temporary recordset with all necessary data, sort in reverse order by LOCATEDATE, and then perform a check with MOVEFIRST and NEXT to see if the associated MARKETPRICE was decent, ie not a value of -5.25. Can anyone help me with the code for this situation? I know that I have made a horrible start, but my brain is fried!! Oh...should I have used some form of the APPLY FILTER method instead?


Private Sub cmbFindLastEssentialData_Click()
Dim dbsStockLoanMaster As Database
Dim rstTempTable As Recordset
Dim strMessage As String
Dim intCommand As Integer

Set dbsStockLoanMaster = OpenDatabase("Stock Loan Master.mdb")
Set rstTempTable = dbs.OpenRecordset( _
"SELECT DailyPrice.LocateDate, DailyPrice.Symbol, DailyPrice.MarketPrice " & _
"FROM DailyPrice, TempSymbol " & _
"WHERE (((DailyPrice.Symbol)=[TempSymbol]![Symbol]));")


With rstTempTable
' Populate Recordset.
.MoveLast
'.MoveFirst
Debug.Print !LocateDate, !Symbol, !MarketPrice
End With
Oct 19 '06 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmbFindLastEssentialData_Click()
  3. Dim db As Database
  4. Dim rs1 As Recordset
  5. Dim rs2 As Recordset
  6. Dim strSQL As String
  7.  
  8. strSQL = "SELECT DailyPrice.LocateDate, DailyPrice.Symbol, " & _
  9. "DailyPrice.MarketPrice FROM DailyPrice " & _
  10. "ORDER BY DailyPrice.LocateDate DESC;"
  11.  
  12. Set db = OpenDatabase("Stock Loan Master.mdb")
  13. Set rs1 = db.OpenRecordset(strSQL)
  14. Set rs2 = db.OpenRecordset("TempSymbol")
  15.  
  16. rs2.MoveFirst
  17. rs1.MoveFirst
  18. Do Until rs2.EOF 
  19.    Do Until rs1.EOF
  20.    If rs2!Symbol=rs1!Symbol Then
  21.       If rs1!MarketPrice <> -5.25 Then
  22.          Debug.Print rs1!LocateDate, rs1!Symbol, rs1!MarketPrice
  23.          rs1.MoveLast
  24.       Else
  25.          rs1.MoveNext
  26.       End If
  27.    Else
  28.       rs1.MoveNext
  29.    End If
  30.    Loop
  31.    rs2.MoveNext
  32. Loop
  33.  
  34. rs1.Close
  35. rs2.Close
  36. Set rs1 = Nothing
  37. Set rs2 = Nothing
  38. Set db = Nothing
  39.  
  40. End Sub
  41.  
  42.  
Oct 20 '06 #2

P: 98
thank you for this code! When I ran it, it said it couldn't find my database, so I changed the code to "set db = currentdb". also it had trouble with the declaration of rst1 and rst2. it gave an error of "datatype mismatch". I declared these as VARIANT instead of DATABASE and it ran ok. Do you know why? Did I do the wrong thing? thank you!
Oct 23 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
rs1 and rs2 are recordsets not variants. In the VB Editor, go to Tools = References and make sure that the Microsoft DAO library is ticked .

then try:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmbFindLastEssentialData_Click()
  3. Dim db As Database
  4. Dim rs1 As DAO.Recordset
  5. Dim rs2 As DAO.Recordset
  6. Dim strSQL As String
  7.  
  8. strSQL = "SELECT DailyPrice.LocateDate, DailyPrice.Symbol, " & _
  9. "DailyPrice.MarketPrice FROM DailyPrice " & _
  10. "ORDER BY DailyPrice.LocateDate DESC;"
  11.  
  12. Set db = CurrentDb
  13. Set rs1 = db.OpenRecordset(strSQL)
  14. Set rs2 = db.OpenRecordset("TempSymbol")
  15.  
  16.  



thank you for this code! When I ran it, it said it couldn't find my database, so I changed the code to "set db = currentdb". also it had trouble with the declaration of rst1 and rst2. it gave an error of "datatype mismatch". I declared these as VARIANT instead of DATABASE and it ran ok. Do you know why? Did I do the wrong thing? thank you!
Oct 23 '06 #4

P: 98
Thank you very much...the DAO was it...runs great!
Oct 23 '06 #5

P: 98
One last question I can't figure out...In my TempSymbol table I have 3 symbol: FFCH,LINE and OLP. This is the order they are in my table. I haven't sorted them at all. Any reason why when I run the code you helped me with, rs2.movefirst is FFCH .....rs2.movenext gives OLP.....rs2.movenext gives LINE. why doesn't it maintain the order of the table TempSymbol?
Thank you again!
Oct 23 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Access imposes its own sort order on a recordset. Unfortunately that doesn't follow any logic that I know of. You could try a ascending sort if it's important.

"SELECT Symbol FROM TempSymbol ORDER BY Symbol ASC;"
Oct 23 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.