• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

Why items in a listbox won't sort properly

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
SteveL13
Asked:
SteveL13
  • 3
  • 2
  • 2
  • +3
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
SteveL13Author Commented:
No.  It is a number value in the table and query behind the listbox.
0
 
PatHartmanCommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
SteveL13Author Commented:
I'm formatting it in the query to get rid of the decimals.

Bay: Format([tblBayQuantities].[Bay],"0")
0
 
Dale FyeCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
PatHartmanCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
hnasrCommented:
Try:

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

Open in new window

0
 
Gustav BrockCIOCommented:
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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now