My unit numbers don't order correctly...

[rbecker]

I am greener than green on Access but I am learning as I go along. I'm
trying to set up a database to keep track of our company's vechicles and when
the registrations expire. I have them organized in an Excel spreadsheet that
has been imported into Access. My problem is when it puts the unit numbers
in order, it lists them as 1, 10, 101, 102, 2, 20, etc. I want them to order
1 through whatever going 1, 2, 3, etc.

How can I make Access do that? Again, I'm greener than green and don't know
much about writing codes or macros or anything

 

[Ken Snell \(MVP\)]

It appears that those numeric values have been stored in a text datatype
field. The sorting order that you're seeing is the the typical sorting order
for text.

You'll either need to change the field's datatype to Number (probably Long
Integer will be the right Field Size property), or you'll need to use a
calculated field in a query that will convert the text numeric characters to
real numbers (using CLng function) -- example for the design grid:

MyNumbers: CLng(YourFieldNameGoesHere)

 

[rbecker]

Thanks Ken, that did help. However, I have two vehicles that didn't convert
correctly which is why it was labled as text to begin with. The two vehicle
numbers have letters in them (C35 and 11A). How would I go about getting
those formatted correctly or is there no hope of doing that?

 

[Ken Snell \(MVP\)]

How should they be "formatted" when they contain both letters and numbers?
I'm not understanding -- are you talking about the datatype that the field
needs to be for these data (the datatype needs to be Text)? Or about sorting
order?

In a query, you can use a calculated field for converting a text number to a
real number to use in sorting. Here's an example of an SQL statement that
would do that:

SELECT * FROM TableName
ORDER BY Val([NameOfTextFieldWithNumbers]);

Note that the Val function will return the numeric value of the numeric
digits, starting from left, until a letter is reached. If the first
character in the string is a letter, Val will return a zero as its value.

 

[Pat Hartman \(MVP\)]

With a mixture of text and numbers, the data type needs to be text. In
order to sort the data "numerically', you need to convert it to a number.
If your letters are leading and/or trailing, use three fields to store the
ID - prefix, numberpart, suffix. That will allow you to sort properly. You
can concatenate them for display purposes. Use an autonumber as the PK but
create a unique index for the three-part unique identifier.

 

[rbecker]

Ok let me clarify my vehicle numbers a little. Most of our vehicle numbers
are three digits with the exception of some cars which are two. One truck
has C35 as its unit number and one car has 11A as its unit number. I am
wanting the database to go by the unit number as the defining field/primary
key because that is each vehicle's unique number. If I keep the datatype as
text it orderes the unit numbers like 1, 10, 102, 104, etc which I don't
want. If I switch the datatype to number, the two vehicles that I mentioned
above come back as errors and either go blank or turn to a 0, but all the
others are in order 1, 2, 3, etc which is the way that I want it. My problem
is with the two above mentioned unit numbers. How should I fix this or can
I? I don't know anything about codes or macros or anything like that.....I'm
just learning about Access. By the way, when I swiched the datatype to
number, C35 went blank and 11A came back a 0.

How should they be "formatted" when they contain both letters and numbers?
I'm not understanding -- are you talking about the datatype that the field
needs to be for these data (the datatype needs to be Text)? Or about sorting
order?

In a query, you can use a calculated field for converting a text number to a
real number to use in sorting. Here's an example of an SQL statement that
would do that:

SELECT * FROM TableName
ORDER BY Val([NameOfTextFieldWithNumbers]);

Note that the Val function will return the numeric value of the numeric
digits, starting from left, until a letter is reached. If the first
character in the string is a letter, Val will return a zero as its value.
--

Ken Snell
<MS ACCESS MVP>

Thanks Ken, that did help. However, I have two vehicles that didn't
convert
correctly which is why it was labled as text to begin with. The two
vehicle
numbers have letters in them (C35 and 11A). How would I go about getting
those formatted correctly or is there no hope of doing that?

 

[John W. Vinson]

One truck
has C35 as its unit number and one car has 11A as its unit number.

Where should C35 come in the sort order? Between 35 and 36? At the beginning?
At the end?

You've got a hodgepodge. Your best bet may be to use a Text field and run an
Update query updating the vehicle numbers to

IIF(IsNumeric([VehNo], Right("000" & [VehNo], 3), [VehNo])

to update 11 to 011, 7 to 007 (that would be Mr. Bond's black Lamberghini),
and C35 to C35. The leading zeros will make the numeric-valued ID's sort
correctly.

John W. Vinson [MVP]

 

[rbecker]

John & Pat, thanks for the suggestions. Now where do I go to find out how
exactly to do that? I'm greener than green with Access.