?
Solved

Why items in a listbox won't sort properly

Posted on 2014-01-17
10
Medium Priority
?
423 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 85
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 38

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 48

Expert Comment

by:Dale Fye
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 85
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 38

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 85
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 51

Accepted Solution

by:
Gustav Brock earned 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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 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 …
Suggested Courses

770 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