Need help sorting the output of a VB query

[stacygrrl]

Greetings...

Just inherited an Access database which tracks clients and events, and issues invitations to active users. When selecting clients to be invited to events, a list is presented with the many thousands of names unsorted. We would like to have this list sorted by LastName, then by FirstName

I traced the source of the list back to this bit of code which I would like to modify so that clients are sorted. I'm sure it's just a simple addition to the code, but for the life of me I can't get it to work.

Dim strQuery As String
strQuery = "SELECT (s.Salutation & ' ' & c.FirstName & ' ' & c.LastName & IIf(t.Title Is Not Null,(', ' & t.Title),' ')) AS ClientName FROM (tblClient AS c LEFT JOIN qryTitleFilter AS t ON c.Title = t.TitleID) LEFT JOIN qrySalutationFilter AS s ON c.Salutation = s.SalutationID WHERE c.ClientID = " & intClientID & ";"



There is some concatenation at work here which may complicate things.

I hope I'm being clear here...I am not a user of VB at all, so please let me know if I've left any important information out of this post.

Thanks!
Stacy

 

[fredericofonseca]

This has nothing to do with VB.

It is a SQL question and should be presented on the correct SQL next time you have a similar issue.

Add the "order by" clause to the SQL statement

strQuery = "SELECT (s.Salutation & ' ' & c.FirstName & ' ' & c.LastName & IIf(t.Title Is Not Null,(', ' & t.Title),' ')) AS ClientName FROM (tblClient AS c LEFT JOIN qryTitleFilter AS t ON c.Title = t.TitleID) LEFT JOIN qrySalutationFilter AS s ON c.Salutation = s.SalutationID WHERE c.ClientID = " & intClientID & "order by field1, field2;"

where field1 and field 2 are the fields you wish to have sorted by

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[EBGreen]

You will probably get an adequate answer here, but you would probably get a faster more accurate answer in one of the VBA forums and more specifically in an Access forum.

In the vein of a quick answer, try sticking "ORDER BY c.LastName, c.FirstName" on the end of your query.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[EBGreen]

Woops. typed too slow.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]