Modified search, requery but how to sort?

[duthiedon]

In my database, the user can select certain fields to search the records. Everything works fine, except the list is not sorted. I've tried placing the sort within the associated query, but this causes an error when I try to run it. How do I sort the results of the requery? I should note, that this is associated with a sub-form, just in case.

Here's the current code:

Private Sub SearchRecords()


Dim QueryDef As QueryDef, db As Database, strSql As String, strOriginalSQL As String
Dim iSplit As Integer
' Modify the Query.

Set db = CurrentDb
Set QueryDef = db.QueryDefs("qrysfrmDocumentSearch")
strOriginalSQL = QueryDef.SQL
QueryDef.Close

iSplit = InStr(strOriginalSQL, ";")
strSql = Left(strOriginalSQL, iSplit - 1)
strSql = strSql & " Where SubmissionDate Between " & DateTimeSQL(txtlnkDateFrom) & " and " & DateTimeSQL(txtlnkDateTo) & " "

If txtDocumentID <> "" Then
strSql = strSql & " And tqryDocuments.Documentid = " & intdocid
End If
If Len(strDocType) > 0 Then
strSql = strSql & " And DocumentType in (" & strDocType & ") "
End If

If Len(strClient) > 0 Then
strSql = strSql & " And Client in (" & strClient & ") "
End If


If intAuthor > 0 Then
strSql = strSql & " And Author = " & intAuthor
End If


If Len(strContractType) > 0 Then
strSql = strSql & " And ContractType = """ & strContractType & """ "
End If

If Len(strDocTitle) > 0 Then
strSql = strSql & " And DocumentTitle Like """ & strDocTitle & """ "
End If
If Len(strDocDesc) > 0 Then
strSql = strSql & " And DocumentDescription Like """ & strDocDesc & """ "
End If
If Len(strAudience) > 0 Then
strSql = strSql & " And Audience in (" & strAudience & ") "
End If
If Len(strLang) > 0 Then
strSql = strSql & " And Language = """ & strLang & """ "
End If
strSql = strSql & ";"

Me.sfrmDocumentSearch.Form.RecordSource = strSql

sfrmDocumentSearch.Requery
QueryDef.Close

End Sub

 

[pbaldy]

You would add an ORDER BY clause to the SQL. What did your effort to do that look like? Presumably the syntax was off. This may help:

http://www.baldyweb.com/ImmediateWindow.htm

 

[duthiedon]

In the query associated with the sub-form, I just added the sort to the field. It should've been straightforward, but for some reason I get an error.

I should mention that I've inherited this database, so I'm not a guru by any means. Any help is greatly appreciated.

Thanks,
Don


I added the "debug.print" and this is the result:

SELECT DISTINCTROW tqryDocuments.DocumentID, qrycmbEmployee.Employee AS AuthorDesc, tqryDocuments.ContractType AS ContractType, tqryDocuments.Author, qrycmbdoctype.CodeDescription AS DocumentType, tqryDocuments.DocumentTitle, tqryDocuments.SubmissionDate, tqryDocuments.Draft, tqryDocuments.Version, qrycmbLanguage.CodeDescription AS LanguageDesc, FindAudience(tqrydocuments.documentid) AS Audiencelst, findclients(tqrydocuments.documentid) AS clientlst, tqryDocuments.DocumentDescription
FROM (((((tqryDocuments LEFT JOIN qrycmbdoctype ON tqryDocuments.DocumentType = qrycmbdoctype.CodeID) LEFT JOIN qrycmbLanguage ON tqryDocuments.Language = qrycmbLanguage.CodeID) LEFT JOIN qrycmbEmployee ON tqryDocuments.Author = qrycmbEmployee.EmpID) LEFT JOIN tqryDocumentsAudience ON tqryDocuments.DocumentID = tqryDocumentsAudience.DocumentID) LEFT JOIN tqryDocumentsClient ON tqryDocuments.DocumentID = tqryDocumentsClient.DocumentID) INNER JOIN tblContractType ON tqryDocuments.ContractType = tblContractType.IDContractType Where SubmissionDate Between #12:00:00 AM# and #12/31/9999# And DocumentType in ("Con") And Client in ("STS") And Language = "Eng" ;

 

[duthiedon]

Forgot to mention, that when I put the "order by...." clause in the associated query, the error message I get is "Run-time error "3138" Syntax error in ORDER BY clause"

 

[pbaldy]

Because of how you start with a query's SQL, you have to add the sort in this code. Otherwise the syntax would be off.

 

[duthiedon]

How do I do that?

 

[pbaldy]

Along the lines of changing this:

strSql = strSql & ";"

to

strSql = strSql & " ORDER BY FieldName;"

 

[duthiedon]

Excellent! Thank you very much for your help!

 

[pbaldy]

No problemo!