• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

Understanding VB6, Access 2000, DAO, SQL Queries

Hello Experts;

I'm a new VB programmer and I'm writing an application to manipulate stock market data. I have an Access 2000 database that I process using VB6, referencing DAO. I want to declare two record sets and using one recordset as an input stream, cycle through the second recordset and process the data.

For the first recordset, I need to retreicve all columns of the first 25  rows sorted in decending sequence by NumFieldA.

The second recordset is the entire database.

Now, I need to find the symbol for each row in recordset 1 in recordset 2 and perform certain processing.

My question: Can anyone help me code the SQL SELECT statements to do the above? I can provide additional information if needed. Thanks for your help!

Joe
0
JoeWaynick
Asked:
JoeWaynick
  • 3
  • 3
1 Solution
 
peter57rCommented:
"My question: Can anyone help me code the SQL SELECT statements "
"For the first recordset, I need to retreicve all columns of the first 25  rows sorted in decending sequence by NumFieldA."

Select top 25 mytable.* from mytable order by NumfieldA desc

"The second recordset is the entire database."

I assume you mean the entire table.

Select * from mytable

Pete
0
 
JoeWaynickAuthor Commented:
Hi Pete;

I added the following code and ran into a syntax error on the first select statement:

    Set dbHist = OpenDatabase("c:\Documents and Settings\Joe\Data Files\MS-Visual Basic\SMA\HistoricalData.mdb")
    Set rs = dbHist.OpenRecordset("tblHistoricalData ", 2)
    Set rsPctChg = dbHist.OpenRecordset("tblHistoricalData ", 2)
   
    If rs.RecordCount > 0 Then
        SELECT TOP 25 rsPctChg.* FROM rs ORDER BY PercentChange desc
        SELECT * FROM rs ORDER BY DateFld, Symbol
    End If

I'm sorry to ask such basic questions, but I'm really new. Any assistance you and others can give is appreciated.

Joe
0
 
peter57rCommented:
I don't really follow what you are doing in your SQL statements, but things don't work like that.
For the top 25 records you probably want:
 Set rs = dbHist.OpenRecordset("SELECT TOP 25 * FROM tblHistoricalData ORDER BY PercentChange desc", 2)

This is what you need for the 'whole table'
Set rsPctChg = dbHist.OpenRecordset(" SELECT * FROM tblHistoricalData ORDER BY DateFld, Symbol ", 2)

It looks like you need to do some reading and look at examples of recordset processing before getting much further.

Pete
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
JoeWaynickAuthor Commented:
Hi Pete;

Thanks for your response. I'll be the first to admit my weakness in understanding vb. And rest assured I'm reading daily to improve my understanding.

Yet, in spite of my unsophisticated explaination of the problem, you still seemed to have captured precisely what I'm trying to do. However, I do need to make one little tweek. I need the top 25 for each date (identified by DateFld), and I'll be cycling through the table building groups of 25 symbols day. I tried modifying your code as follows but it didn't work. Can you give me a hint as to where I'm wrong?

Your original code:    
                           Set rsPctChg = dbHist.OpenRecordset("SELECT TOP 5 * FROM tblHistoricalData
                                ORDER BY PercentChange desc", 2)

My change:
                          Set rsPctChg = dbHist.OpenRecordset("SELECT TOP 5 * FROM tblHistoricalData
                               WHERE DateFld = dteDateVar ORDER BY PercentChange desc", 2)

dteDateVar is a variable I decleared with a starting date and I will increment the date after each group of top 25 symbols. When I run my change I get:

                         "Runtime 3061. Too few parameters. Expected 1."

Thanks again for any assistance you and others can provide.

Joe

0
 
peter57rCommented:
Set rsPctChg = dbHist.OpenRecordset("SELECT TOP 5 * FROM tblHistoricalData
                               WHERE DateFld = #" & dteDateVar  & "# ORDER BY PercentChange desc", 2)
Dates must be enclosed in #..#  and you need the variable to be evaluated so it has to be 'outside' the sql string.

Pete
0
 
JoeWaynickAuthor Commented:
Pete;

Thank you for the modified code. It works like a champ. More importantly, thanks for the explaination as to "why" it works. I'll tuck that away in my toolkit. Your points are well deserved.

Joe
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now