Solved

Why items in a listbox won't sort properly

Posted on 2014-01-17
10
413 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
  • 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 36

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 36

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 49

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

821 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