[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Why items in a listbox won't sort properly

Posted on 2014-01-17
10
Medium Priority
?
426 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 39

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 39

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 31

Expert Comment

by:hnasr
ID: 39790152
Try:

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

Open in new window

0
 
LVL 52

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

649 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