Sort Continueous Form on Two Fields

[khwaja]

I am using the following by Allen Browne to sort one field. But now I need to sort on two fields. I wonder if someone could help revise the code.

Function SortForm(frm As Form, ByVal sOrderBy As String) As Boolean
On Error GoTo Err_SortForm 'Provided by Allen Browne
'Purpose: Set a form's OrderBy to the string. Reverse if already set.
'Return: True if success.
'Usage: Command button above a column in a continuous form:
' Call SortForm(Me, "MyField")
Dim sForm As String ' Form name (for error handler).

sForm = frm.Name
If Len(sOrderBy) > 0 Then
' Reverse the order if already sorted this way.
If frm.OrderByOn And (frm.Orderby = sOrderBy) Then
sOrderBy = sOrderBy & " DESC"
End If
frm.Orderby = sOrderBy
frm.OrderByOn = True
' Succeeded.
SortForm = True
End If

Exit_SortForm:
Exit Function


Err_SortForm: ' Label to jump to on error.
MsgBox err.Number & err.Description ' Place error handling here.

'Call LogError(Err.Number, Err.Description, conMod & ".SortForm()","Form'= " & sForm & "; OrderBy = " & sOrderBy)
Resume Exit_SortForm
End Function


Private Sub Command33_Click()

Call SortForm(Me, "[Priority]")

End Sub

 

[Galaxiom]

Just list both fields separated by a comma in the sOrderBy parameter.

 

[khwaja]

I thought about it but when I add a coma, the prompt is for true or false. I continued anyway but there is an error (wrong number of arguments or invalid property assignment).

PS: may be you meant to put it in the main function. I tried on Call sortForm ... If so what is the linje I need to amend?

 

[Galaxiom]

The SOrderBy is a string and must be delimited by double quotes. The comma must be inside that string.

Don't change the function just call it like this:

Call SortForm(Forms!formname, "firstfieldname, secondfieldname")

 

[khwaja]

One of the field I am sorting is number and the other is date. So would this still be a string?

I tried the suggested fix but it still does not work. Compile error, wrong number of arguments.

 

[Galaxiom]

Datatypes don't matter. It is a simply list of the fieldnames as a single string.

 

[khwaja]

Thanks. I tried the code as under with necessary changes as suggested but I keep getting an error when I hit the command button to sort.

Function SortForm(frm As Form, ByVal sOrderBy As String) As Boolean
On Error GoTo Err_SortForm 'Provided by Allen Browne
'Purpose: Set a form's OrderBy to the string. Reverse if already set.
'Return: True if success.
'Usage: Command button above a column in a continuous form:
' Call SortForm(Me, "MyField")
Dim sForm As String ' Form name (for error handler).

sForm = frm.Name
If Len(sOrderBy) > 0 Then
' Reverse the order if already sorted this way.
If frm.OrderByOn And (frm.Orderby = sOrderBy) Then
sOrderBy = sOrderBy & " DESC"
End If
frm.Orderby = sOrderBy
frm.OrderByOn = True
' Succeeded.
SortForm = True
End If

Exit_SortForm:
Exit Function


Err_SortForm: ' Label to jump to on error.
MsgBox err.Number & err.Description ' Place error handling here.

'Call LogError(Err.Number, Err.Description, conMod & ".SortForm()","Form'= " & sForm & "; OrderBy = " & sOrderBy)
Resume Exit_SortForm
End Function


Private Sub Command33_Click()

Call SortForm(Me, "Priority", "DDueBy")

End Sub

 

[Galaxiom]

Call SortForm(Me, "Priority", "DDueBy")

You need to read my replies more closely.
The second parameter is a single string.

Call SortForm(Me, "Priority, DDueBy")

 

[khwaja]

I apologise and thank you for being patient. Will try.

 

[khwaja]

Great it works now but I seem to have null values preceding numbers. I tried to use

Expr1: IIf(IsNull([Priority]),1,0)

in the underlying query but perhaps it works only when I open the form but when I sort null values appear the at top.

 

[Galaxiom]

If you sort on the derived field then you will get the zeros first which will be the same as Nulls first.

As it is written, the automatic reversing on the function will only work on the last field in the list.

BTW Use Nz() function instead of the Iif and IsNull. It will perform much better.