ADO Recordset Sort Problem 
Author Message
 ADO Recordset Sort Problem

I am trying to sort a database record using a variable for the sort field:

        recordset.sort = combo1.text

The above statement works fine, and sorts in ASCENDING order.
I need help with sorting on combo1.text in DESCENDING order.
I can do this with SQL, but would rather not because combo1 has about 20
fields.  SQL would therefore require 20 statements.


Joe



Sun, 08 Aug 2004 08:07:15 GMT  
 ADO Recordset Sort Problem
try this

recordset.sort = "recordset!namefield = '" & combo1.text & "'"


Quote:
> I am trying to sort a database record using a variable for the sort field:

>         recordset.sort = combo1.text

> The above statement works fine, and sorts in ASCENDING order.
> I need help with sorting on combo1.text in DESCENDING order.
> I can do this with SQL, but would rather not because combo1 has about 20
> fields.  SQL would therefore require 20 statements.


> Joe



Sun, 08 Aug 2004 10:47:07 GMT  
 ADO Recordset Sort Problem
Thanks for your reply, but the code did not work.
I got "runtime error 3001"

Joe


Quote:
> try this

> recordset.sort = "recordset!namefield = '" & combo1.text & "'"



> > I am trying to sort a database record using a variable for the sort
field:

> >         recordset.sort = combo1.text

> > The above statement works fine, and sorts in ASCENDING order.
> > I need help with sorting on combo1.text in DESCENDING order.
> > I can do this with SQL, but would rather not because combo1 has about 20
> > fields.  SQL would therefore require 20 statements.


> > Joe



Sun, 08 Aug 2004 21:03:59 GMT  
 ADO Recordset Sort Problem
Joe

What you need is

MyRecset.Sort = "ProgramID ASC"

will sort MyRecSet by column ProgramID ASCending

similarly

MyRecset.Sort = "ProgramID DES"

will sort MyRecSet by column ProgramID DEScending

similarly

MyRecset.Sort = "ProgramID ASC, UserName DESC"

will sort MyRecSet by column ProgramID ASCending and within that
UserName Descending

Quote:

> Thanks for your reply, but the code did not work.
> I got "runtime error 3001"

> Joe



> > try this

> > recordset.sort = "recordset!namefield = '" & combo1.text & "'"



> > > I am trying to sort a database record using a variable for the sort
> field:

> > >         recordset.sort = combo1.text

> > > The above statement works fine, and sorts in ASCENDING order.
> > > I need help with sorting on combo1.text in DESCENDING order.
> > > I can do this with SQL, but would rather not because combo1 has about 20
> > > fields.  SQL would therefore require 20 statements.


> > > Joe



Tue, 10 Aug 2004 00:26:57 GMT  
 ADO Recordset Sort Problem
Thanks for your help, but the problem I am having is that I have about 20
columns.
Each is listed in a combobox, and I want to be able to sort on the selected
text;
combo1.text.

Any other suggestion?

Joe


Quote:
> Joe

> What you need is

> MyRecset.Sort = "ProgramID ASC"

> will sort MyRecSet by column ProgramID ASCending

> similarly

> MyRecset.Sort = "ProgramID DES"

> will sort MyRecSet by column ProgramID DEScending

> similarly

> MyRecset.Sort = "ProgramID ASC, UserName DESC"

> will sort MyRecSet by column ProgramID ASCending and within that
> UserName Descending


> > Thanks for your reply, but the code did not work.
> > I got "runtime error 3001"

> > Joe



> > > try this

> > > recordset.sort = "recordset!namefield = '" & combo1.text & "'"



> > > > I am trying to sort a database record using a variable for the sort
> > field:

> > > >         recordset.sort = combo1.text

> > > > The above statement works fine, and sorts in ASCENDING order.
> > > > I need help with sorting on combo1.text in DESCENDING order.
> > > > I can do this with SQL, but would rather not because combo1 has
about 20
> > > > fields.  SQL would therefore require 20 statements.


> > > > Joe



Tue, 10 Aug 2004 08:26:10 GMT  
 ADO Recordset Sort Problem
This may work:

Select Case Screen.ActiveControl
  Case "combo1"
       Do your filter....
  Case "combo2"
       Diff filter

End Select

it will tell you which control has focus when the event you want fires (Left
mouse click maybe?).

HTH



Wed, 11 Aug 2004 11:00:54 GMT  
 ADO Recordset Sort Problem
I'm confused Joe. Why would SQL require 20 statements? You say you are only
using one variable for the sort field, so why not construct a SQL statement
that encompasses the one variable, in the form of:

SELECT Whatever FROM Wherever ORDER BY 'ChosenFieldName' DESC


Quote:
> Thanks for your reply, but the code did not work.
> I got "runtime error 3001"

> Joe



> > try this

> > recordset.sort = "recordset!namefield = '" & combo1.text & "'"



> > > I am trying to sort a database record using a variable for the sort
> field:

> > >         recordset.sort = combo1.text

> > > The above statement works fine, and sorts in ASCENDING order.
> > > I need help with sorting on combo1.text in DESCENDING order.
> > > I can do this with SQL, but would rather not because combo1 has about
20
> > > fields.  SQL would therefore require 20 statements.


> > > Joe



Wed, 11 Aug 2004 18:29:32 GMT  
 ADO Recordset Sort Problem
Rob, thanks for your response.  I am sorry if I did not make myself clear.
Combo1.text has a list of 20 field names.  Can I write a single SQL
statement that
will allow my to sort DESC on the highlighted combo1.text?

Thanks.

Joe


Quote:
> I'm confused Joe. Why would SQL require 20 statements? You say you are
only
> using one variable for the sort field, so why not construct a SQL
statement
> that encompasses the one variable, in the form of:

> SELECT Whatever FROM Wherever ORDER BY 'ChosenFieldName' DESC



> > Thanks for your reply, but the code did not work.
> > I got "runtime error 3001"

> > Joe



> > > try this

> > > recordset.sort = "recordset!namefield = '" & combo1.text & "'"



> > > > I am trying to sort a database record using a variable for the sort
> > field:

> > > >         recordset.sort = combo1.text

> > > > The above statement works fine, and sorts in ASCENDING order.
> > > > I need help with sorting on combo1.text in DESCENDING order.
> > > > I can do this with SQL, but would rather not because combo1 has
about
> 20
> > > > fields.  SQL would therefore require 20 statements.


> > > > Joe



Wed, 11 Aug 2004 22:13:08 GMT  
 ADO Recordset Sort Problem
Have you tried recordset.sort = combo1.text & " DES" or recordset.sort =
combo1.text & " ASC"

You could also set up an option group with 2 choices  (Ascending and
Descending) which sets a variable and then have recordset.sort = combo1.text
& varOption


Quote:
> I am trying to sort a database record using a variable for the sort field:

>         recordset.sort = combo1.text

> The above statement works fine, and sorts in ASCENDING order.
> I need help with sorting on combo1.text in DESCENDING order.
> I can do this with SQL, but would rather not because combo1 has about 20
> fields.  SQL would therefore require 20 statements.


> Joe



Sat, 14 Aug 2004 16:56:15 GMT  
 ADO Recordset Sort Problem

Thanks Michael.  Your suggestions worked.

Joe


Quote:
> Have you tried recordset.sort = combo1.text & " DES" or recordset.sort =
> combo1.text & " ASC"

> You could also set up an option group with 2 choices  (Ascending and
> Descending) which sets a variable and then have recordset.sort =
combo1.text
> & varOption



> > I am trying to sort a database record using a variable for the sort
field:

> >         recordset.sort = combo1.text

> > The above statement works fine, and sorts in ASCENDING order.
> > I need help with sorting on combo1.text in DESCENDING order.
> > I can do this with SQL, but would rather not because combo1 has about 20
> > fields.  SQL would therefore require 20 statements.


> > Joe



Wed, 18 Aug 2004 20:15:42 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. ADO recordset sorting problem

2. ASP-ADO-RecordSet-Sort problem

3. ADO Recordset .Sort failure...

4. Sorting an ADO Recordset

5. Need help with ADO recordset sorting issue

6. Sorting ADO Recordset

7. ADO Recordset .Sort failure...

8. Sorting ADO Recordset

9. ADO SORT property on recordset with DESC keyword

10. VB6 Function to Sort ADO Recordset

11. Sort property of ADO Recordset

12. ADO Recordset no initial sort

 

 
Powered by phpBB® Forum Software