Help with a strange SQL/VB query problem 3

[stacygrrl]

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 VB 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.

' initialize a string for the query
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 & ";"


I have tried to tack on the ORDER BY element at the end in various ways but it doesn't want to work. When I do it returns an error and the VB debugger highlights the following line which is this:

' open the record set
Dim adoRs As New ADODB.Recordset
adoRs.Open strQuery, CurrentProject.Connection, adOpenStatic, adLockOptimistic, adCmdText

I hope I'm being clear here...I am really an advanced Access user, so please let me know if I've left any important information out of this post.

Thanks!
Stacy

 

[lespaul]

Try this:

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 c.LastName, c.FirstName;"

Leslie

 

[stacygrrl]

Thanks for the reply lespaul. I did what you suggested and it gave me the following error:

Syntax error (missing operator) in query expression 'c.ClientID = 1ORDER BY c.LastName,c.FirstName'

I have no idea why this happens?

Thanks again...

 

[dhookom]

Check to make sure you entered the expression as lespaul suggested. The error message points directly at the missing space before the "ORDER BY".
[blue]" & intClientID & " ORDER BY c.LastName, c.FirstName;" [/blue]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[amorous]

You may also want to try the below query: made little changes to the query suggested by lespaul

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 = [b]'" & intClientID & "'[/b] ORDER BY c.LastName, c.FirstName;"

Observe the single quotes.

hope that helps.

VJ

 

[lespaul]

except you only need the ' in the SQL if intClientID is a string field in the database. I assumed that it was a number and therefore didn't need the '. Plus Stacy said her original query works, she just wanted it sorted. So all I did was take her original query and add the string needed to order it as requested. Unfortunately, she didn't copy and paste what I did, which is why she got the error with the space.

Leslie

 

[stacygrrl]

Thanks very much for all your help and suggestions. Unfortunately none of them worked and our list remains unsorted. I am positive that I copied the suggestions exactly. Strangely, if I include the ORDER BY statement the query still functions exactly as it did before. The ORDER BY statement is just ignored. I am really groping in the darkness here as I am a novice Access user at best.

One thing that makes me wonder is that concatenation that is at work in the SELECT expression - could this have an effect?

Otherwise I am tearing my hair out over this. Thanks again for your help everybody.

Stacy

 

[lespaul]

what happens if you run the query in Access instead of through the ASP page? Do the results show up sorted there?

leslie


Leslie

 

[stacygrrl]

Ok it works now. After soliciting all the outside help I could, it was determined that the reason the ORDER BY statement didn't work was that each Name is pulled out and sorted one at a time, the sort wasn't being applied to the whole list. So I added the ORDER BY to another piece of code that actually builds the content of the list box where the names appear and it worked.

Thanks for your help everyone, I really appreciate it!!

Stacy