Sorting on a mingled field

[Karja]

Goodmorning,

I am trying to sort a listbox on a field that is the result of two fields combined with an iff-function. De query works fine untill I try to sort on one of the mingled fields (con1 or con2). The system just prompts for the variable Con1.

Q: How do I tell the system that it should sort on Con1? Below the specific code that is giving me problem.

Sub Contacts_Initialize()
    
listConSQL = "SELECT ID, Organisatie, Achternaam1 & iif(isnull(achternaam1),'',', ') & Voorletters1 as Con1, Achternaam2 & iif(isnull(achternaam2),'', ', ') & Voorletters2 as Con2 " & _
             "FROM tbl_Contacten"
orderConSQL = "Con1 ASC"
    
With Form_Contacts
     .listContacts.RowSource = listConSQL & " ORDER BY " & orderConSQL
     Set RsClone = .RecordsetClone
     .listContacts.Value = RsClone![ID]
     Set RsClone = Nothing
End With
End Sub

Thanks ahead in time, any help appreciated.

 

[PHV]

Use the ordinal position of the field:
orderConSQL = "3 ASC" ' Con1
orderConSQL = "4 ASC" ' Con2

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

 

[Karja]

Perfect. Just what I needed. Many thanks!