Sorting of a Column in the Continuous Form

[khwaja]

I got this code written by Allen Brown and it works great. But the only concern I have is that when I click the command button once (Call SortForm(Me, "[Priority]"), it sorts my values (priority expressed in numbers) in descending order fine but the ascending order shows blank rows first followed by correct ascending order. Is there something I can do to tweak this to drop blank rows at the end?


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

 

[Galaxiom]

Sort on a derived field something like:
SortField: Nz(fieldname,9999999)

 

[khwaja]

Thanks. I changed the syntax to the following but it does not quite do anything now.

Call SortForm(Me, "NZ([Priority], 999)")

 

[Galaxiom]

Include the derived SortField in the RecordSource query of the form. Then use its name in the function argument.

 

[khwaja]

OK thanks. I created a seperate field and added to the underlying query.

FPriority: NZ([Priority],999)

Then I changed my statement as below. But still getting 999 in blank columns.

Call SortForm(Me, "[FPriority]")

 

[Galaxiom]

Just display the original Priority field.

The FPriority field needs to be in the Recordset of the form but does not need to be displayed so don't include a textbox for it.

 

[khwaja]

So you want me to add this additional field in the query on it own, have it sorted here. What is the correct format to have the field contents listed so that blanks or null are the bottom. I am using the following but then all zeros are the top.

FPriority: Val(NZ([Priority]))

 

[Galaxiom]

Val(Nz(Priority,99999))

The Val() would be required because the Nz function will return a string and make the field a text type if it isn't forced to a number. Strings sort alphabetically.

Sometimes developers use CInt() or CLng() instead of Val() provided the values are below the limits of these types. Val() is possibly the best choice because it will take big numbers.

Just use a larger number in the Nz than the biggest possible real value.

 

[khwaja]

Thanks. Tried FPriority: Val(NZ([Priority, 999999])) but when run it returns Enter parameters doalogue box.

 

[Galaxiom]

Square bracket misplaced in Val(NZ([Priority, 999999]))

Val(NZ([Priority], 999999))

BTW Square brackets are only needed when there are spaces or special characters in the name. It is one good reason to never use spaces and special characters in names.

 

[khwaja]

Thanks one again. I removed square brackets and now I can run the query but the result is similar to the below. I thought we were getting a zero where there were no values.

FPriority Priority
99999
99999
99999
4 4
99999
99999
99999

 

[Galaxiom]

Apply a Sort to FPriority in the query design.

If you want a zero on the Nulls in Priority then derive another field and display that instead of priority.:

Priority1: Nz(Priority, 0)

 

[khwaja]

Thank you for persevering with this. I am not having much luck. I tried the proposed change. Following is my SQL. Pl see if these can be amended in some ways.

SELECT tblStores.StoreID, tblStores.Location, NZ([Priority],0) AS Expr1, Val(NZ([Priority],99999)) AS FPriority, tblProjects.Priority
FROM tblStores INNER JOIN tblProjects ON tblStores.StoreID = tblProjects.StoreID
ORDER BY tblStores.Location, NZ([Priority],0) DESC , Val(NZ([Priority],99999));

 

[Galaxiom]

I think I have confused your requirement. I thought you wanted ascending then Nulls.

If you want Descending then Nulls just use zero or even a negative number instead of 9999 for the NZ

ORDER BY tblStores.Location, NZ([Priority],0) DESC

 

[khwaja]

I have a lot of records with blank priorities but a handfull have numbers in it. So my interest is to see numbers first ranging from 1 to whatever the last number followed by blanks. DESC works fine but not quite ASC.

 

[vbaInet]

When you sort in DESC order the blank records will stay at the bottom but when you sort in ASC they come on top. So you want your derived field to reflect this too:

FPriority: Nz([Priority], IIF([COLOR=Red]SortOrder()[/COLOR] = "ASC", 9999, Null))

SortOrder() is a function that you will create that will return the type of sort you want to apply before applying the sort on the form.

 

[khwaja]

Thanks a lot.

 

[vbaInet]

No problemo!