Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 3rd, 2004, 10:45 AM
Registered User
 
Join Date: Nov 2003
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADO + Open Table

I have a table that is sorted ASC by three columns: CPT4, MOD and EFFDATE in that order. So, of course it sorts by CPT4 first, MOD second and EFFDATE third.

There are multiple rows for each CPT4 and there are different dates for each CPT4. I am going through the table one record at a time and adding an entry in the 'flag' column to tell if the record does not have the most current date for the CPT4.

Problem: When the ADO recordset is navigating through the table it does not keep my sort that I did in the table. This is a problem because my whole code depends upon these three columns being sorted the way they are.

Here is my function that I am using:

Function SortOutVANTAG05()
    Dim rst As New ADODB.Recordset

    Dim First As Date
    Dim Second As Date

    rst.Open "SELECT * FROM VANTAG05", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

    Second = #1/1/2005#

    Do Until rst.EOF

        First = rst!EFFDATE

        If First > Second Then

            rst.MovePrevious
            rst!flag = "Not the Current One"
            rst.MoveNext

        End If

        Second = First

        rst.MoveNext

    Loop

End Function

Thanks for the help.

Reply With Quote
  #2 (permalink)  
Old February 3rd, 2004, 10:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Just change your SQL so that the recordset is sorted in the way you want:
SELECT * FROM VANTAG05 ORDER BY CPT4, MOD, EFFDATE
Reply With Quote
  #3 (permalink)  
Old February 3rd, 2004, 11:16 AM
Registered User
 
Join Date: Nov 2003
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Perfect.

Thanks a lot.

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
ADO recordset to table kanoorani VB Databases Basics 3 May 16th, 2006 02:59 AM
problem using ADO to open form in MS Access apike VBScript 3 May 2nd, 2006 06:25 AM
Create table from another table using ADO Kaustav Pro VB Databases 0 October 15th, 2005 05:44 AM
Backup an open access database (ado) jolzy VB Databases Basics 0 October 27th, 2004 09:38 AM
ADO connecting to a Paradox 8.0 table cpopham Access VBA 0 December 5th, 2003 12:22 PM



All times are GMT -4. The time now is 03:02 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.