?
Solved

How to do select query in descending order in VBA Programming

Posted on 2009-02-19
14
Medium Priority
?
482 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.
0
Comment
Question by:logicslab
  • 4
  • 4
  • 4
  • +2
14 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23679315
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
 
LVL 11

Expert Comment

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

Author Comment

by:logicslab
ID: 23680329
expecting a better solution
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 6

Expert Comment

by:jparul
ID: 23681822
Could you post your query and the specific settings you are using?
0
 

Author Comment

by:logicslab
ID: 23689338
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
 
LVL 11

Expert Comment

by:mildurait
ID: 23689383
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
 
LVL 11

Expert Comment

by:mildurait
ID: 23689455
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
 

Author Comment

by:logicslab
ID: 23689457
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 23689492
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 23689528
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
 
LVL 18

Accepted Solution

by:
jmoss111 earned 750 total points
ID: 23689576
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
 

Author Comment

by:logicslab
ID: 23708657
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 23708919
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
 
LVL 11

Expert Comment

by:mildurait
ID: 23709029
Logicslab... how about awarding points as you see fit and asking another Q?
0

Featured Post

Industry Leaders: 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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 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