Order by (Sorting) question

[Kenln]

I have a query

SELECT tbl_Task_Standards.Task, tbl_Task_Standards.Description
FROM tbl_Task_Standards
ORDER BY Val([Task]), tbl_Task_Standards.Task;

This works fine, no prob; Note the order by.

On a command button I have

    Me.sfrm_Main.Form.OrderByOn = False
    Me.sfrm_Main.Form.OrderBy = "[Area], val([Task]), [Task]"
    Me.sfrm_Main.Form.OrderByOn = True

On a different one I have

    Me.sfrm_Main.Form.OrderByOn = False
    Me.sfrm_Main.Form.OrderBy = "val([Task]), [Task], [Area]"
    Me.sfrm_Main.Form.OrderByOn = True

However on the form using the code in the command buttons it does not order by val([Task]). It is like I have not included that part at all.

Tasks are

01
02
2B
2C
5A
6A
14
22
30

Which display fine when the form is opened. When I click the command button they display.

01
02
14
22
2B
2C
30
5A
6A

Am I missing something???

Thank you for any help,

 

[Alisa]

The second list looks sorted to me. What is wrong with it? Is the problem that 2B comes after 22? That is because you are sorting text, not numbers. If that is the issue, you need to have a second column in your data source that is a true number to sort by . . .

 

[Kenln]

In the first list 2 (or even 2A), 3, 4, comes before 14. This sort order is numerical first, then alphabetical.

In the second list anything begining with a '1' is first so 1, 14, 133, even 15617 is before 2. Then the '2's: 2, 2a, 24, 27, 3, 32, etc... This list is strickly alphabetically sorted rather than numerical first.

Ther order differently even though both the queriy and the code is:

val([Task]), [Task]

 

[doco]

If Task is a text data type and can have content of '2' or '2A' then attempting to convert Task to VAL() will not do the trick. As an option and as suggested, you will need to provide another column with the numeric value and sort it.

Or make certain all of your single digit numbers have a leading zero. EG. 02A, 03A, etc. Then the sort order will be as you desire

01A
02A
01B
02C
03A
.
.
.
55C

 

[Kenln]

According to the help for val.

Returns the numbers contained in a string as a numeric value [...] The Val function stops reading the string at the first character it can't recognize as part of a number.

And this does actually work as part of a query. It does not work however, when I call it from code in an 'OrderBy'.

I can add a field as an extra number, but this would be my last choice since I would have to manually enter the entire list and if there is ever a task inserted in the middle I would have to redo the list from that point again.

Yep! All of the single digits have a leading zero.

 

[Alisa]

I can add a field as an extra number, but this would be my last choice since I would have to manually enter the entire list and if there is ever a task inserted in the middle I would have to redo the list from that point again.

Why would it have to be done manually? Just write a query that updates your numbering column to = val(task) . . .

 

[Kenln]

Good idea,

Unfortunately, it is part of a larger field and used in several queries. Editing each query to sort by a new table that is created programitically isn't the best solution on this project.

I think it is easier to not use the Val() since it does not work consistently. It isn't what I wanted but the sort order will remain the same from one screen to another.

 

[Alisa]

Good idea,

Unfortunately, it is part of a larger field and used in several queries. Editing each query to sort by a new table that is created programitically isn't the best solution on this project.

I think it is easier to not use the Val() since it does not work consistently. It isn't what I wanted but the sort order will remain the same from one screen to another.

But you wouldn't have to change any existing queries - they are working fine right? You would only need this one column to fix the sort order on your form.

 

[Kenln]

I'm sorry but I am not following.
Correct the queries are working fine.
The subform works/displays fine when opened, used, printed, viewed etc...

If I programatically change the order of the subform and back using the above, (just like the queries) then they no longer sort like the queries.

Where would I get the extra column on the forms?

 

[Alisa]

You would need to add the sorting field to the underlying table - then you can add it to the recordsource of your form.

 

[Kenln]

Yes that is a good idea except I would only have to edit the form(s) the sort exists. Rather than added another physical field to the underlying table, I probably could just add an expression to the query that is being used. Making that expression visible should work in a sort.

Thanks, I'll try that.