Sorting

[darkmastergyz]

Hi! I have a form right now, which links to a table with 1000 or so records. I currently have a search page, and the search works well, by using an access filter. I'm wondering how I can sort. Is there a command for it? How can I use it? Thanks.

 

[Fernando]

Forms have OrderBy and OrderByOn properties
Lets say you want to order by the field: fldName
You just need to fill the OrderBy Property to "[fldName]" to sort in acendant order or "[fldName] Desc" to decendant
You can also create a button (cmdSort) and on click event:

Private Sub cmdSort_Click()

Me.OrderBy = "fldName"
Me.OrderByOn = True

End Sub
Always make sure the OrderByOn Propery is set to True

 

[boblarson]

And, just as an addendum, you can sort by multiple fields by using

Me.OrderBy = "fldName,fld2Name"

and if you wanted to use descending order in there you could use

Me.OrderBy = "fldName DESC,fld2Name"

 

[darkmastergyz]

Awesome! This works well. The only thing is though that null values come up to the top. Also, it'd be cool if an small arrow, pointing up, or down, will sort by it, you know like in many databases. do you know how to do that? Thanks.

 

[Fernando]

Hi again, first of all i dont think you should have nulls on your records, please take some time and read this topic:

Nulls: Should it ever be meaningful?
http://www.access-programmers.co.uk/forums/showthread.php?t=131571

If you don't want to show where the nulls are just add to the filter "fldName is Not Null" (if you have more than one criteria go for example like this "fld2Name > 0 AND fldName is Not Null)

I know lol, the small arrow is cool. To create that effect u can create a bmp (bitmap Image) on paint very little like 4x4 pixels, one pointing upwards and another pointing downwards and put them next to the label of the field (imgAscendant, imgDecendant). Then on Label Click (lblfldName) event do something like this.

Sub lblfldName_Click()

If Me.OrderBy = "fldName" Then
Me.Orderby = "fldName Desc"
Me.imgAscendant.Visible = False
Me.imgDecendant.Visible = True
Else
Me.Orderby = "fldName"
Me.imgAscendant.Visible = True
Me.imgDecendant.Visible = False
End If

End Sub

 

[darkmastergyz]

Awesome! That works on most of my fields, except, I have one field which is numeric, and it doesn't work, and I have one field, which is a check box, which brings up a dialog asking me to enter a parameter or something. How can I use the sort for check boxes? Thanks.

 

[Fernando]

It may be because you didn't wrote the field name correctly on the code.

instead of
Me.OrderBy = "fldName"
maybe you wrote
Me.OrderBy = "fdName"

You can use on Boolean Types too (True/False, On/Off) if thats what you mean by checkboxes

 

[The_Doc_Man]

Fernando's right on the mark but left off one other possibility.

You get the "enter parameter" dialog box when you spell the field name wrong. You ALSO get it when you are trying to pull the data from a place where it isn't - like from a text box on a form that isn't open or something very much similar.