Sort in a Query

  • Thread starter Thread starter Don Hamilton
  • Start date Start date
D

Don Hamilton

I have a data base that includes decimal sizes that need to be sorted such
as numbers like 2.5 and 25 or 5.5 and 55. All numbers are between 2.5 and
70.5. I can make the Query sort but it ignores decimal points. I am new to
this and appreciate all the help. I hope there is a simple answer. Thanks
Don
 
They may look like numbers but it sounds like Access/JET is sorting them as
Text. IIRC, Access/Jet 4 ignores punctuation characters when performing
search.

Are you aware of any reason why these are being treated as Text strings,
e.g. Format() function returns (variant of) Text type?

You can, of course, use functions such as Val() or CSng() or CDbl() to
convert these Strings to Numeric.
 
Don said:
I have a data base that includes decimal sizes that need to be sorted
such as numbers like 2.5 and 25 or 5.5 and 55. All numbers are
between 2.5 and
70.5. I can make the Query sort but it ignores decimal points. I am
new to this and appreciate all the help. I hope there is a simple
answer. Thanks Don
Click to expand...

Make sure the field property in the underlying table is set to number format
and set decimal places to Auto.

Terry D.
 
Terry D said:
Make sure the field property in the underlying table is set to number format
and set decimal places to Auto.

Terry D.
Click to expand...
Thanks for the help but when I change it to number it deletes all my data
from that field. It is probably because that field also contains text as in
(2.5 x 14) as in (bore x stroke) of industrial gas compressor cylinders.
When I sort ascending it starts with 10 x 14 even though 2.5 x 14 is
smaller. I did read that by adding 02.5 x 14 it will then make it first, but
the data base contains 10,000 entries and this will be a major problem with
many cylinders smaller than 10 x 14. Thanks Don
 
You are storing Text but you want Access to sort numerically which Access
got no way of knowing.

Fortunately, if the Text always starts with the digits for the bore
diameter, you can use the Val() function to evaluate the bore diameter as a
Calculated Field in your Query and sort the rows according to this
Calculated value. For example:

?Val("2.5 x 12.9 whatever")
2.5
 
Don Hamilton said:
Thanks for the help but when I change it to number it deletes all my data
from that field. It is probably because that field also contains text as in
(2.5 x 14) as in (bore x stroke) of industrial gas compressor cylinders.
When I sort ascending it starts with 10 x 14 even though 2.5 x 14 is
smaller. I did read that by adding 02.5 x 14 it will then make it first, but
the data base contains 10,000 entries and this will be a major problem with
many cylinders smaller than 10 x 14. Thanks Don
Click to expand...

As Mr. Dinh has pointed out, that data, while it might look like numbers to
you and me, is simply text to Access. As text, 1 will always sort before 2,
regardless of what is after the 1. If you wish to do sorting, or for that
matter, searching or reporting, using that data as numeric, you probably
shouldn't be storing both the bore and the stroke in the same field. Bore
and Stroke should each be an attribute, of numeric type, in the table of
cylinders.
 
Van T. Dinh said:
You are storing Text but you want Access to sort numerically which Access
got no way of knowing.

Fortunately, if the Text always starts with the digits for the bore
diameter, you can use the Val() function to evaluate the bore diameter as a
Calculated Field in your Query and sort the rows according to this
Calculated value. For example:

?Val("2.5 x 12.9 whatever")
2.5
Click to expand...

Wher do I insert this Val() function in my query? Where is the Calculated
Field entry? I am very new to Access. My query has Field, Table, Sort, Show,
Criteria, and Or. I am using Access 2002 if that matters. Thanks for the
help with some one who is lost. Don
 
In an empty Column of your Query Grid, click on the "Field" row and enter :

MySortNumber: Val([FieldName])

replacing [FieldName] with the name of your Field. For this Column, leave
the "Table" row empty, check "Show" (so that you can see what is returned by
Val() but you can uncheck it later) and select [Sort Ascending] in the
"Sort" row. When you run the Query, the Records are sorted by the values of
the Val() function.

This Column is called Calculated Field / Value since it is calculated /
derived from values of other Field(s) / Column(s).

All of this is covered in virtually all Access books. If you haven't got
any book, suggest you get one and start reading as you won't go far without
reading at least one Access book.
 
Thanks Van, I have Access 2000 for Dummy's but if you could suggest a better
I will go that way. Don

Van T. Dinh said:
In an empty Column of your Query Grid, click on the "Field" row and enter :

MySortNumber: Val([FieldName])

replacing [FieldName] with the name of your Field. For this Column, leave
the "Table" row empty, check "Show" (so that you can see what is returned by
Val() but you can uncheck it later) and select [Sort Ascending] in the
"Sort" row. When you run the Query, the Records are sorted by the values of
the Val() function.

This Column is called Calculated Field / Value since it is calculated /
derived from values of other Field(s) / Column(s).

All of this is covered in virtually all Access books. If you haven't got
any book, suggest you get one and start reading as you won't go far without
reading at least one Access book.

--
HTH
Van T. Dinh
MVP (Access)



Don Hamilton said:
Wher do I insert this Val() function in my query? Where is the Calculated
Field entry? I am very new to Access. My query has Field, Table, Sort, Show,
Criteria, and Or. I am using Access 2002 if that matters. Thanks for the
help with some one who is lost. Don
Click to expand...
Click to expand...
 
Back
Top