We help IT Professionals succeed at work.

How to do select query in descending order in VBA Programming

Medium Priority
549 Views
Last Modified: 2012-05-06
I want to do a select query with descending order in MS Access ,VBA programming. I am getting the result in ascending order . But I need the result in descending order. I have set the order (desc) in coding an also set the OrderByOn =true. some times it is working , but  getting only 4 records. I have above 500 records.
It is the adp application and I am using SQL server 2005 as the database.
It is an urgent need ,So please help
Thanks in advance for your response.
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
If this is your Select Query

select col1, col2, col3 from table

then include the order clause as

select col1, col2, col3 from table order by col1
CERTIFIED EXPERT

Commented:
try
dim sql as string
sql = "select col1, col2, col3 from table order by col1 desc"
me.recordsource = sql
me.requery

Author

Commented:
expecting a better solution

Commented:
Could you post your query and the specific settings you are using?

Author

Commented:
My sql query is ' SELECT      TOP (100) PERCENT CodeClient, RTRIM(FirstName) + ' ' + LTRIM(LastName) AS Name, ISNULL(Address, '') + ' ' + ISNULL(City, '') + ', ' + ISNULL(State,
                        '') + ' ' + ISNULL(Zipcode, '') AS Address, DOB, TelHome, TelCel, TelWork, DriverLicense, DateSignup, NotesClient, HowContactUs, TotalPaid AS Paid,
                        DueDate, GrantTotalPayments AS AmountPaid, DueDate1
FROM          dbo.Clients  '
it is directly binding in the page . I have some options for finding clients  like
1.Name
2.Date of Birth
3.Driver License
4.Client code
5.Citation no
6.All clients
I need to select any of these options and that will append to the query when the program executes . I have no problem when i am selecting any of the options and display results in ascending order.But I need the results in descending order .
I have set the order in the coding .
it is
me.Subform_ClientsResumed.Form.OrderByOn=True
me.Subform_ClientsResumed.Form.OrderBy="CodeClient DESC"
my SQL query is directly bind in Clients Resumed(Queries).And I am getting records in Clients Resumed when it open in design view.But im getting only 4 records when I run the application and those records are not continuous (random selection).
Please give a solution for this .

thanks
CERTIFIED EXPERT

Commented:
Logics Lab
Have you tried inverting these code lines as below?
me.Subform_ClientsResumed.Form.OrderBy="CodeClient DESC"
me.Subform_ClientsResumed.Form.OrderByOn=True

Have you tried saving your sql as a query together with the ORDER BY clause and running the query?
Do you get the same results?
CERTIFIED EXPERT

Commented:
i have to sign off for the weekend, but some more suggestion to help you get to the source of this problem.
a) What  happens when you use the base fields (ie without concatenations) in the form?
b) Are the concatenated fields all of the same data type?

Author

Commented:
Yea I I have tried that too.But no use.
Is there any way to see the records in the recordset while the program running ?.
I have no problem with Access 2003 version. But I need it in Access2007 and the problem is there.

Commented:
Hi logicslab,

What datatype is the column you're ordering on, and is the column used in a WHERE and ORDER BY? Like jparul, seeing the SQL would be nice. The more info provided, the better and quicker you'll be assisted. Also, index info on the underlying table might be helpful for optimization.

Regards,

Jim

Commented:
Sorry about my last post which looks out of place; I opened earlier and didn't realize that I hadn't refreshed the page...
Commented:
Have you thought about building a nonclustered index with the columns you're using in selection criteria included in name descending order? Would likely prevent a table scan in building the query. Also why top 100%? And you're returning all rows and filtering?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Hi
Thanks for your valuable comments ,Now it is working in correct order . But I need your help to fix another problem.
I have button named cmdMailLabels . When I click on that, I will redirect to another form where I can select 2 dates to display preview of a schedule (schedule for clients).Date picker is set for both text boxes  (from date and to date).
Here we need to set a format for date in the text boxes property window.
In MSAccess 2003 the format of date is mm/dd/yyyy (eg : 8/27/2008) .O S I used here is Windows XP
Im MSAccess 2007 the format of date is  dd-mm-yyyy (eg : 27-08-2008).OS I used here is Windows 2008.
There is no 'dd-mm-yyyy ' format in MSAccess 2003  also no 'mm/dd/yyyy ' format in MSAccess 2007. It is working in MSAccess 2003 .But when I execute the same application in MSACccess 2007 , I getting date converting error message.But I need to do it in MSAccess 2007 .
So please suggest  a solution.
Thanks in advance

Commented:
If the original question has been answered, then the question should be closed and points awarded. If you need help to fix another problem, then you need to ask another question.
CERTIFIED EXPERT

Commented:
Logicslab... how about awarding points as you see fit and asking another Q?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.