Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sorting continuous form

Status
Not open for further replies.

GelC

Technical User
Oct 3, 2006
93
US
Hi all!
I have a search form with continuous returned results.
Now, I desire to add options for sorting the results.

Form record source: tblAllRes
Code:
Private Sub cmdSearch_Click()

Dim strWhere As String
Dim lngLen As Long
Dim strOrderBy As String

    If Not IsNull(Me.txtKeywords) Then
        strWhere = strWhere & "[Keywords] Like ""*" & Me.txtKeywords & "*"" AND "

    If Not IsNull(Me.cboYear) Then
        strWhere = strWhere & "([Year] Like """ & Me.cboYear & """) AND "
    End If
    
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "Please enter at least a criteria"       
    Else

        If (Me.ogSort.Value = 1) Then
            strOrderBy = " ORDER BY tblAllRes.ResID "
        ElseIf (Me.ogSort.Value = 2) Then
            strOrderBy = " ORDER BY tblAllRes.Title "
        ElseIf (Me.ogSort.Value = 3) Then
            strOrderBy = " ORDER BY tblAllRes.Year "
        End If

        strWhere = Left$(strWhere, lngLen)
        Me.Filter = strWhere & "" & strOrderBy
        Me.FilterOn = True              
                
    End If
    
Exit_cmdSearch_Click:
    Exit Sub

Err_cmdSearch_Click:
    MsgBox Err.Description
    Resume Exit_cmdSearch_Click
End Sub
I keep getting error msg "Syntax error (missing operator) in query expression."
Could anyone please help me on this problem?

Thank you in advanced.

Gelc
 
Code:
...
        If Me!ogSort.Value = 2 Then
            strOrderBy = "Title"
        ElseIf Me!ogSort.Value = 3 Then
            strOrderBy = "[Year]"
        Else
            strOrderBy = "ResID"
        End If
        Me.Filter = Left$(strWhere, lngLen)
        Me.FilterOn = True
        Me.OrderBy = strOrderBy
        Me.OrderByOn = True
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
PHV,

You are my survivor always.
:)
Many thanks!!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top