Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ado Recordset filtering

Posted on 2011-02-10
2
Medium Priority
?
391 Views
Last Modified: 2012-05-11
Hi,
I have using a query to open a recordset where I have three fields namely Amount, TransNo, TransDate. When I open the recordset in the win form  I will have to loop through this recordset and compare each of the amount against the value user entered.
i.e. If RS.Fileds("Amount") <= txtAmount Then.....

How do I loop through this in ADO Recordset as this can be done only in VB script? Moreover, there might be occasion where multiple records might meet the criteria in that case I need to requery the recordset and select the vary latest one using the transdate field as filtering criteria.
i.e.
Amount        TransDate                transNo
29                  12/01/2011             RT2568
20                   05/01/2011            QR2148    
45                   01/02/2011             RY2531
Say, If I use Criteria If Amount <= 50 Then all three will be qulifying this criteria so I willl select the first one because that is the latest one in this recordset.

Please someone help me?  
0
Comment
Question by:ube100
[X]
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
2 Comments
 
LVL 1

Accepted Solution

by:
Srinivasan__ earned 2000 total points
ID: 34868673
The vbScript routine will search for the value and the recordset.

Pass the query with the order by for TransDate column with the Descending order
Set objCon = Server.CreateObject("ADODB.Connection")
Set ObjRS = Server.CreateObject("ADODB.Recordset")


ObjCon.Open Connection_String
ObjRs.Open <sqlstatement>,objCon,3,4

3,4 - adOpenKeySet, adLockPessimistic

sub CheckRecordSet(objRS, Value)
    If NOT ObjRS.BOF OR objRS.EOF Then 
    While NOT objRS.EOF
          If objRS.Fields("Amount")<=Value)
           -- Do Something --
          End If
    Wend
    End If
End Sub

Query : Select Amount, TransDate, TransID from <table> order by TransDate DESC

Open in new window

0
 
LVL 16

Expert Comment

by:AlexPace
ID: 34872312
I would seriously consider changing the SQL statement to only return the valid data. You could use a subquery to select only the max date with a matching amount.  This will return a single date.  Then that date is used in the WHERE criteria of your outer query, so it only returns records with the requested amount on the max date. For good measure sort them descending by transaction number.  This way the first record in your recordset will always be the one you were looking for and if you happen to have any others in the recodset they will be others with the same amount also on the same day so you can show them or not.
MySQL = "SELECT * FROM TheTable " & _
        "WHERE Amount = " & txtAmount & _
        " AND TransDate = (" & _
        "SELECT MAX(TransDate) FROM TheTable " & _
        "WHERE Amount = " & txtAmount & ") " & _
        "ORDER BY transNo DESC"

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

722 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