• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

How to do select query in descending order in VBA Programming

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.
0
logicslab
Asked:
logicslab
  • 4
  • 4
  • 4
  • +2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
0
 
milduraitCommented:
try
dim sql as string
sql = "select col1, col2, col3 from table order by col1 desc"
me.recordsource = sql
me.requery
0
 
logicslabAuthor Commented:
expecting a better solution
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jparulCommented:
Could you post your query and the specific settings you are using?
0
 
logicslabAuthor 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
0
 
milduraitCommented:
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?
0
 
milduraitCommented:
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?

0
 
logicslabAuthor 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.
0
 
jmoss111Commented:
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
0
 
jmoss111Commented:
Sorry about my last post which looks out of place; I opened earlier and didn't realize that I hadn't refreshed the page...
0
 
jmoss111Commented:
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?
0
 
logicslabAuthor 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
0
 
jmoss111Commented:
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.
0
 
milduraitCommented:
Logicslab... how about awarding points as you see fit and asking another Q?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now