Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Understanding VB6, Access 2000, DAO, SQL Queries

Posted on 2006-07-12
6
381 Views
Last Modified: 2013-12-25
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
Comment
Question by:JoeWaynick
  • 3
  • 3
6 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 17098589
"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
 

Author Comment

by:JoeWaynick
ID: 17099144
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
 
LVL 77

Expert Comment

by:peter57r
ID: 17102941
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:JoeWaynick
ID: 17103632
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
 
LVL 77

Accepted Solution

by:
peter57r earned 200 total points
ID: 17106722
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
 

Author Comment

by:JoeWaynick
ID: 17107825
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
passing parameter in sql procedure 9 65
Passing a Text Box name to a Sub 6 99
backup program with robocopy 6 45
VB script to continue despite error 2 16
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question