me.filter and sort?

[JudyHNM]

I have the following code which filters a form for me, and it works fine, but I would like to add a sort order (based on two fields) to it. How can I do that?

Private Sub cmdFilter_Click()
'Purpose:   Create dynamic screen report using form frmVersionList
'Date:      04/30/17




Dim strWhere    As String       'Criteria string
Dim lngLen      As Long         'Length of criteria string to append to


'********************************************************************
'Look at search box(es) and build criteria string from non-blank ones
'********************************************************************
    
    If Not IsNull(Me.txtFilterDesignName) Then
        strWhere = strWhere & "([tblP_DesignName] LIKE ""*" & Me.txtFilterDesignName & "*"")"
    End If


'********************************************************************
'Apply string as the form's filter
'********************************************************************
    Me.Filter = strWhere
    Me.FilterOn = True
    


End Sub

Thanks, Judy

 

[ranman256]

The sort order is part of the WHERE. Make a query and view its SQL.

 

[JudyHNM]

The sort order is part of the WHERE. Make a query and view its SQL.

I understand what you are saying by I can't figure out how to add the order by part to the where statement. Can you provide any help with that?

thanks, Judy

 

[ranman256]

Add on the end: ORDER BY [field]

(just like a query does it)

 

[JudyHNM]

Add on the end: ORDER BY [field]

(just like a query does it)

Sorry, I should have been more specific. I did realize that I needed "ORDER BY [field]", but I can't get the syntax/format/punctuation right.

I have tried both of the ones shown below plus some other variations, and I continually get Error 3075 "missing operator.

strWhere = strWhere & "([tblP_DesignName] LIKE ""*" & Me.txtFilterDesignName & "*"") & Order by [tblP_Designname]"       
strWhere = strWhere & "([tblP_DesignName] LIKE ""*" & Me.txtFilterDesignName & "*"") & Order by [tblP_Designname]"""

Can you either provide or point me to an example that will show the correct format?

Thanks, Judy