Solved

Why items in a listbox won't sort properly

Posted on 2014-01-17
10
415 Views
Last Modified: 2014-01-18
I have a listbox on a form with 2 columns.  The first one I want to sort numerically in ascending order.

It is a number field.

But the way it is sorting now is:

1
124
2

and I want it to be

1
2
124

What am I doing wrong?
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 84
ID: 39789825
I would assume you have a Text value. Try casting the field as a Numeric type. For example, if you're using this query as the Listbox Rowsource:

SELECT MyTextCol FROM MyTable

Use this instead:

SELECT CDbl(MyTextCol) AS MyCastColumn FROM MyTable
0
 

Author Comment

by:SteveL13
ID: 39789832
No.  It is a number value in the table and query behind the listbox.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39789848
It may be a numeric value but the column definition of the table says it is text.  If it were defined as a number, it would sort as a number rather than as a string.  Are you formatting the column in the query or concatenating it with a string?  Either will turn a number into text.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:SteveL13
ID: 39789862
I'm formatting it in the query to get rid of the decimals.

Bay: Format([tblBayQuantities].[Bay],"0")
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39789864
Do you use the NZ( ) function anywhere in the query for that column?  If so, Access will convert it to a string in the query, (see Access Help on the NZ() function), so you will have to explicitly type it as an integer or long, similar to what Scott mentioned above.

SELECT cLng(NZ([yourField], 0)) as myConvertedField FROM yourTable
0
 
LVL 84
ID: 39789884
I'm formatting it in the query to get rid of the decimals.
That's where it's coming from. Try using CInt instead, since that will always show only the Integer portion:

Bay:CInt(Bay)
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39789888
Format() turns a number into a string.  Use cInt() instead.

Bay: cInt([tblBayQuantities].[Bay])

There are other options for getting rid of the decimals but still remaining numeric.  Off hand, I don't remember if cInt() will round or truncate.

You could also try just setting the decimal positions to 0 for that column although I don't know if the listbox honors that setting or not.
0
 
LVL 84
ID: 39789904
Off hand, I don't remember if cInt() will round or truncate.
CInt, CLng etc do round. If you don't want that, use INT or FIX instead:

Bay: Int(Bay)

or

Bay:Fix(Bay)
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39790152
Try:

Select fld1, fld2 from tbl Order By CLng(fld1)

Open in new window

0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39790493
In you query where you have:

    Bay: Format([tblBayQuantities].[Bay],"0")

add a new column:

    BaySorted: Val(Format([tblBayQuantities].[Bay],"0"))

and Order by for this to Ascending and remove sorting from other columns.

/gustav
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question