Sorting a continuous form based on a field

[mcomp72]

I have a continuous form where the user can change what is displayed on it by selecting a date in a combo box in the header area.

Then, they can sort the records by clicking a couple of the text labels in the header section. One of the ones they can click is called Over/Under.

Here's the code that is tied to the On Click event of that text label.

Private Sub OverUnder_Label_Click()

'This will sort the results by OverUnder.
Me.OrderBy = "OverUnder DESC"
Me.OrderByOn = True
Me.Refresh

End Sub

It works correctly sometimes, but not others.

Here's a screenshot from when it is working correctly.

This is correct, because it is sorting the numbers in the column by highest to lowest.

But when I change the selection in the Date combo box and then click Over/Under, it does the sort but not correctly. Here's a screenshot.

I experimented and found that if I removed the "DESC" from the line of code in the On Click sub, here is how it sorts the two examples above.

Now the latter one is correct (almost), but the former is wrong.

I don't understand why it is doing this.

Another related question: I would like any record where Over/Under is either Null or doesn't contain a number to be at the bottom of the sort. Ones with numbers should always come first. In some instances, it seems like that is happening (at least when it comes to a Null value) but other times it is not.

 

[bob fitz]

I can't replicate the problem.
Can you post a copy of the db

 

[mcomp72]

Here you go.

Dropbox

www.dropbox.com

Open the Sign-in Sheet form. If the date is not already 7/4/23, select that one. Clicking Over/Under should work. Then change the date to 7/27/23. Clicking Over/Under should not work. (It will sort, but not in the correct order.) You can also try 7/31/23. That one doesn't sort correctly, either. (If you click the Last Name text label, it will sort the form that way.)

FYI - I don't know if it matters, but the numbers showing up in Over/Under are generated by a function. The function, named CalculateOverUnder, returns a Variant. (Usually it returns a number, but occasionally it may return a string.) It can be found in Module1.

 

[Gasman]

Why can't you just upload it here?

Edit: Too late a version for me anyway.

 

[bob fitz]

Here you go.

Dropbox

www.dropbox.com

Open the Sign-in Sheet form. If the date is not already 7/4/23, select that one. Clicking Over/Under should work. Then change the date to 7/27/23. Clicking Over/Under should not work. (It will sort, but not in the correct order.) You can also try 7/31/23. That one doesn't sort correctly, either. (If you click the Last Name text label, it will sort the form that way.)

FYI - I don't know if it matters, but the numbers showing up in Over/Under are generated by a function. The function, named CalculateOverUnder, returns a Variant. (Usually it returns a number, but occasionally it may return a string.) It can be found in Module1.

Take a look at the attached db.
I have changed your UDF to return an integer rather than a variant which I believe solved the problem.
I have also changed the SQL statement used in the forms Recordsource so it no longer uses your UDF but does the calculation within the query instead.

 

[mcomp72]

Thanks, Bob. Unfortunately, your solution won't work for me. You may have missed it in my post, but the UDF will sometimes need to return a string. That is why I have it return a variant. Sometimes the value in the Points Needed column will be a string, and thus a straight math calculation can't always happen.

Is there another way to solve this problem that doesn't involve changing/removing the UDF?

 

[bob fitz]

Thanks, Bob. Unfortunately, your solution won't work for me. You may have missed it in my post, but the UDF will sometimes need to return a string. That is why I have it return a variant. Sometimes the value in the Points Needed column will be a string, and thus a straight math calculation can't always happen.

Is there another way to solve this problem that doesn't involve changing/removing the UDF?

Can you post a copy of your db which has a string value being returned in the UDF

 

[mcomp72]

Sure, here you go.

 

[bob fitz]

How do you the column if the values returned are G2, -5,-7

 

[MajP]

Look at comboafterselection. The orderby goes to lastname.

Private Sub AfterComboSelection(WriteTournamentIDtoOptionsTable As Boolean)

Dim ID As Long
Dim rstTOURNAMENTS As DAO.Recordset
Dim SQLstr As String

'This will change what appears in the tbCourseName text box.
Set db = CurrentDb

ID = Me.cboDate.Value

SQLstr = "SELECT * FROM qTournaments WHERE TournamentID = " & ID

Set rstTOURNAMENTS = db.OpenRecordset(SQLstr, dbOpenSnapshot)

tbCourseName.Value = rstTOURNAMENTS![CourseName]

rstTOURNAMENTS.Close
Set rstTOURNAMENTS = Nothing
Set db = Nothing

'This will sort the results by Last Name.
Me.OrderBy = "LastName"

'T

 

[MajP]

Easy test

Private Sub cboDate_AfterUpdate()

Call AfterComboSelection(True)
MsgBox Me.OrderBy
End Sub

 

[mcomp72]

How do you the column if the values returned are G2, -5,-7

Sorry, but I don't understand the question. Can you rephrase?

 

[mcomp72]

Easy test

Private Sub cboDate_AfterUpdate()

Call AfterComboSelection(True)
MsgBox Me.OrderBy
End Sub

Yes, when the user changes what is in the Date combo box, I want the form to be sorted by Last Name.

After that, they can choose to click the Over/Under text label to sort by that column. When I do that in my testing, it is not always sorting that column correctly. It does a sort, but I don't understand how it is determining HOW to sort. It seems to be all over the place. Even if the value in all fields in that column are numbers, it still doesn't sort it correctly.

The way I would like it to sort when clicking that text label is this order:
1. Numbers, highest to lowest
2. Non-numbers (doesn't matter the order)
3. Null or empty string

 

[bob fitz]

Sorry, but I don't understand the question. Can you rephrase?

If the values returned by the UDF are G2, -5, -7 then how would you like them ordered when the label is clicked.
-7 is smaller than -5, so -5 would be above -7, but where would you want the text value (G2) to be. Below -7 or above -5

 

[mcomp72]

If the values returned by the UDF are G2, -5, -7 then how would you like them ordered when the label is clicked.
-7 is smaller than -5, so -5 would be above -7, but where would you want the text value (G2) to be. Below -7 or above -5

The way I would like it to sort when clicking that text label is this order:
1. Numbers, highest to lowest
2. Non-numbers (doesn't matter the order)
3. Null or empty string (doesn't matter the order)

 

[MajP]

I added two fields to the query
OUsort: isnumeric([overunder])
vSort: val([overUnder])

then did this
Me.OrderBy = "ousort asc, vsort desc"

this sorts the numeric first then, by number.

 

[Pat Hartman]

but the UDF will sometimes need to return a string. That is why I have it return a variant.

Numbers sort by magnitude and align at the implied decimal point. Strings sort character by character, left to right. If you want a string to sort the way numbers sort, you would need to zero fill them. So the numbers would be

-0002
 0003
 0007
G2

Positive numbers have a blank where the minus sign would be for a negative number.
I don't remember if Val() handles the minus sign correctly.

 

[mcomp72]

@Pat Hartman - ahhhh, that is very good to know. Thank you.

@MajP - that worked! I implemented it and now it is sorting correctly. Many thanks!!

 

[Pat Hartman]

You're welcome. File that info away and remember to NOT Format() numbers or dates and expect them to sort "logically". But MajP's solution solves your immediate problem.