Order By is not working

[mguwc]

Hi All!

I have created a filter form which allows me to enter criteria in unbound fields and then select records based on this criteria.

There is a search button that shows this event
Private Sub Command20_Click()
Dim stSql As String

stSql = ""
stSql = "SELECT [LastName],[FirstName],[Child Identification],[DOB],"
stSql = stSql & "[ResFName1]& ' ' & [ResLName1] as [Residing With Party 1],"
stSql = stSql & "[ResFName2]& ' ' & [ResLName2]as [Residing with Party 2]"
stSql = stSql & " from [Filter Query]"
stSql = stSql & " Where 1=1 "

If [ClientLastName] <> "" Then
stSql = stSql & " and ([LastName]) like forms![filter form].[ClientLastName]"
End If
If [Child Identification] <> "" Then
stSql = stSql & " and ([child identification]) like forms![filter form].[child identification]"
End If
If [ClientFirstName] <> "" Then
stSql = stSql & " and ([FirstName]) like forms![filter form].[ClientFirstName]"
End If
If [ClientDOB] <> "" Then
stSql = stSql & " and ([DOB]) like forms![filter form].[ClientDOB]"
End If
If [ResidingWithFirstName] <> "" Then
stSql = stSql & " and ([ResFName1]) like forms![filter form].[ResidingWithFirstName]"
stSql = stSql & " or ([ResFName2]) like forms![filter form].[ResidingWithFirstName]"
End If
If [ResidingWithLastName] <> "" Then
stSql = stSql & " and ([ResLName1]) like forms![filter form].[ResidingWithLastName]"
stSql = stSql & " or ([ResLName2]) like forms![filter form].[ResidingWithLastName]"
End If

stSql = stSql & " ORDER BY [ClientLastName];"

Me.List18.RowSource = stSql
End Sub

List18 displays the information, but does not order by [ClientLastName] as shown above.

I have even gone into my filter query and put order by ascending last name.

Is there something I am missing?

 

[hymn]

I think you might need the table name and then the field ie.
stSql = stSql & " ORDER BY YourTableName.ClientLastName;"

Hope this helps
Hymn

 

[randy700]

It appears that ClientLastName is the name of a control on your form but NOT in the query you are pulling your data from. Have you tried "ORDER BY [LastName]" ?


Randy

 

[PHV]

Or simpler: ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244