Solved Sort Query Result by Ascending or Descending (1 Viewer)

[hrdpgajjar]

HI all,
I have a query designed on "Search Button" Click event as under,

Private Sub btnsearch_Click()


    Dim SQL As String
    
    SQL = "SELECT DataBase.ID, DataBase.[Regi No], DataBase.[Farmer Name], Database.[MIS System], Database.[Area (Ha)]" _
    & " FROM [DataBase] " _
    & " WHERE [Farmer Name] LIKE '*" & Me.sb & "*' " _
    & " OR [Regi No] LIKE '*" & Me.sb & "*' " _
    
    
    Me.sublist.Form.RecordSource = SQL
    Me.sublist.Form.Requery
    
    Dim supplyqry As String
    
    supplyqry = "SELECT [Material Order Details].ID, [Material Order Details].[Regi No], [Material Order Details].[Farmer Name], [Material Order Details].[MIS System], [Material Order Details].[Material Supplied Date]" _
    & " FROM [Material Order Details] " _
    & " WHERE [Farmer Name] LIKE '*" & Me.sb & "*' " _
    & " OR [Regi No] LIKE '*" & Me.sb & "*' " _
    
    Me.sublistsupply.Form.RecordSource = supplyqry
    Me.sublistsupply.Form.Requery
    






End Sub

Now I want the result in Descending order. How can I do it.


Thanks !!!!

 

[moke123]

Add an Order by clause to the end of your query.

I dont think I would use Database as a table name as it is an ace and sql reserved word.

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

allenbrowne.com

Also eliminate spaces and special characters in the names of your fields.

 

[arnelgp]

Private Sub btnsearch_Click()


    Dim SQL As String
   
    SQL = "SELECT DataBase.ID, DataBase.[Regi No], DataBase.[Farmer Name], Database.[MIS System], Database.[Area (Ha)]" _
    & " FROM [DataBase] " _
    & " WHERE [Farmer Name] LIKE '*" & Me.sb & "*' " _
    & " OR [Regi No] LIKE '*" & Me.sb & "*' " _
   
   
    With Me.sublist.Form
        .RecordSource = SQL
        .Requery
        .OrderBy = "theField DESC"
        .OrderByOn = True
    End With
   
    Dim supplyqry As String
   
    supplyqry = "SELECT [Material Order Details].ID, [Material Order Details].[Regi No], [Material Order Details].[Farmer Name], [Material Order Details].[MIS System], [Material Order Details].[Material Supplied Date]" _
    & " FROM [Material Order Details] " _
    & " WHERE [Farmer Name] LIKE '*" & Me.sb & "*' " _
    & " OR [Regi No] LIKE '*" & Me.sb & "*' " _
   
    With Me.sublistsupply.Form
        .RecordSource = supplyqry
        .Requery
        .OrderBy = "yourField DESC"
        .OrderByOn = True
    End With
   
   

End Sub

 

[hrdpgajjar]

Private Sub btnsearch_Click()


    Dim SQL As String
 
    SQL = "SELECT DataBase.ID, DataBase.[Regi No], DataBase.[Farmer Name], Database.[MIS System], Database.[Area (Ha)]" _
    & " FROM [DataBase] " _
    & " WHERE [Farmer Name] LIKE '*" & Me.sb & "*' " _
    & " OR [Regi No] LIKE '*" & Me.sb & "*' " _
 
 
    With Me.sublist.Form
        .RecordSource = SQL
        .Requery
        .OrderBy = "theField DESC"
        .OrderByOn = True
    End With
 
    Dim supplyqry As String
 
    supplyqry = "SELECT [Material Order Details].ID, [Material Order Details].[Regi No], [Material Order Details].[Farmer Name], [Material Order Details].[MIS System], [Material Order Details].[Material Supplied Date]" _
    & " FROM [Material Order Details] " _
    & " WHERE [Farmer Name] LIKE '*" & Me.sb & "*' " _
    & " OR [Regi No] LIKE '*" & Me.sb & "*' " _
 
    With Me.sublistsupply.Form
        .RecordSource = supplyqry
        .Requery
        .OrderBy = "yourField DESC"
        .OrderByOn = True
    End With
 
 

End Sub

I have done it but its not working.. any help?

 

[arnelgp]

it will not work unless you put the Correct fieldname to sort with.

 

[hrdpgajjar]

it will not work unless you put the Correct fieldname to sort with.

Sorry for late reply! It works ! thanks