Sorting a select statement within VBA

[Birky]

Can someone help me enter a sort statement into the below SQL statement? For the life of me I can not get it to work and I have tried everything I can think of. I just need this stupid thing to sort on the Element_Name but I can not find the proper syntax to get it to work.

Any help would be greatly appreciated.

Private Sub Project_Name_AfterUpdate()
Dim strSQL As String
Dim intSQL As Integer
 
    Me!Element_Name.RowSource = ""
 
    strSQL = IIf(Me!Project_Name > "", _
                 "SELECT [Element_Name] " & _
                 "FROM [Elements] " & _
                 "WHERE ([Project_Name]='" & Me!Project_Name & "')", _
                 "")
    With Me!Element_Name
        .RowSource = strSQL
        .Enabled = (.ListCount > 0)
    End With
 
'    Me!Element_Name.RowSource = strSQL
 
End Sub

Thanks
Birky

[hariharanmca]

Can someone help me enter a sort statement into the below SQL statement? For the life of me I can not get it to work and I have tried everything I can think of. I just need this stupid thing to sort on the Element_Name but I can not find the proper syntax to get it to work.

Any help would be greatly appreciated.

Private Sub Project_Name_AfterUpdate()
Dim strSQL As String
Dim intSQL As Integer
 
    Me!Element_Name.RowSource = ""
 
    strSQL = IIf(Me!Project_Name > "", _
                 "SELECT [Element_Name] " & _
                 "FROM [Elements] " & _
                 "WHERE ([Project_Name]='" & Me!Project_Name & "')", _
                 "")
    With Me!Element_Name
        .RowSource = strSQL
        .Enabled = (.ListCount > 0)
    End With
 
'    Me!Element_Name.RowSource = strSQL
 
End Sub

Thanks
Birky

Use 'Order By Element_Name' after where Clause

[hariharanmca]

Can someone help me enter a sort statement into the below SQL statement? For the life of me I can not get it to work and I have tried everything I can think of. I just need this stupid thing to sort on the Element_Name but I can not find the proper syntax to get it to work.

Any help would be greatly appreciated.

Private Sub Project_Name_AfterUpdate()
Dim strSQL As String
Dim intSQL As Integer
 
    Me!Element_Name.RowSource = ""
 
    strSQL = IIf(Me!Project_Name > "", _
                 "SELECT [Element_Name] " & _
                 "FROM [Elements] " & _
                 "WHERE ([Project_Name]='" & Me!Project_Name & "')", _
                 "")
    With Me!Element_Name
        .RowSource = strSQL
        .Enabled = (.ListCount > 0)
    End With
 
'    Me!Element_Name.RowSource = strSQL
 
End Sub

Thanks
Birky

Order By Element_Name Asc
Order By Element_Name Desc

[Birky]

That’s what I thought, I had just omitted the “Acs” part. Once I added this to my order by statement everything worked.

Thanks for the help..
Birky

[NeoPa]

The ASC or DESC qualifiers sort Ascending or Descending, but it always defaults to ASC if no qualifier is specified.
You probably had some small error in your SQL before. Frustratingly you had the correct understanding but not the experience and confidence to know that.
It's sorted now, that's the main thing :)