Solved

Sorting an Access table using VBA

Posted on 2012-03-31
4
3,473 Views
Last Modified: 2012-03-31
I'm looking for a simple line or two of code to sort an Access able by a specific field.  Thus far I have tried the following and some variations of it but without success.  I want to sort the field WtdAvgPctGoalMet in descending order.

Dim strSQL As String
    strSQL = "ALTER TABLE [table name] SORT COLUMN WtdAvgPctGoalMet Desc"
    CurrentDb.Execute strSQL

The above produces a syntax error message but gives no clue as to what should be changed.  Any suggestions would be appreciated.
0
Comment
Question by:Liberty4all
  • 2
4 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 37791996
What is your purpose of sorting the data in the actual table when the records can be viewed in a sorted order?  On a form, you could either update the RecordSource property (RowSource property if a listbox) to include the Order By keyword (ie. SELECT [table name] ORDER BY WtdAvgPctGoalMet Desc), or you could just use the OrderBy property on the form itself.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 37791999
try this codes, you have to create a query to do this

Dim strSQL As String, qd As DAO.QueryDef
If DCount("*", "msysobjects", "name='q1'") = 0 Then
   'create the query definition
    strSQL = "select * from TABLENAME order by  WtdAvgPctGoalMet Desc"
    Set qd = CurrentDb.CreateQueryDef("q1", strSQL)
End If
'open the query
   DoCmd.OpenQuery "q1"
0
 

Author Comment

by:Liberty4all
ID: 37792058
My reason for sorting records in the table is to do this prior to using code to add and populate a field that ranks them using the following code.

strSQL = "ALTER TABLE [table name] ADD COLUMN Rank COUNTER"
    CurrentDb.Execute strSQL

The table is populated from two different sources.  This means the values in it are not sorted in descending order which is necessary before running the code to add and populate the Rank field.  I was hoping to use code to perform the sort function first but can obviously do it by adding another query to the mix.  

I will accept the solution offerred by capricorn1 for future use.
0
 

Author Closing Comment

by:Liberty4all
ID: 37792064
See my comments following those of capricorn1.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Web appliction 7 46
Direct Mail software 4 42
MS Access Order Smallest to Biggest Query Help 13 39
Access Excel export not behaving 2 24
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

929 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now