Sorting by Measurements? Access 2K

[Tony]

Hi All,

I've got some continuous forms that display information such as: itemName,
itemPrice, itemSize. On each form, when the user clicks the column heading,
the records will sort either ascending or descending, depending on what the
current sort is. For example, if the the current sort is itemName when the
user clicks the itemName heading the sort is changed to itemName DESC.
Everything works. However, when I get to itemSize, things fall apart
because the sizes are in English units (inches & feet), with a sampling like
this:

1-1/2"
6"
10"

If I apply my current sort method and sort by itemSize, the 6" item falls to
the end of the list since the entries are text (they have to be this way
because itemSize units can be inches, feet, or a combination of both).

I imagine that I could add another column to my table that holds a
conversion of the feet/inces to a decimal, add it as a hidden column on the
form, then use it for sorting. But, I'm wondering if anyone run into this
issue and if so, do you have any tips you can share as to how you addressed
it?

Thanks & Ciao

 

[Dirk Goldgar]

Hi All,

I've got some continuous forms that display information such as: itemName,
itemPrice, itemSize. On each form, when the user clicks the column
heading, the records will sort either ascending or descending, depending
on what the current sort is. For example, if the the current sort is
itemName when the user clicks the itemName heading the sort is changed to
itemName DESC. Everything works. However, when I get to itemSize, things
fall apart because the sizes are in English units (inches & feet), with a
sampling like this:

1-1/2"
6"
10"

If I apply my current sort method and sort by itemSize, the 6" item falls
to the end of the list since the entries are text (they have to be this
way because itemSize units can be inches, feet, or a combination of both).

I imagine that I could add another column to my table that holds a
conversion of the feet/inces to a decimal, add it as a hidden column on
the form, then use it for sorting. But, I'm wondering if anyone run into
this issue and if so, do you have any tips you can share as to how you
addressed it?

(my first reply to this post seems to have been eaten by the net-news
monsters)

You don't need to create a separate field for this, so long as you can write
a function that converts the itemSize to a number value in standard units.
For example, if you had a function fncSizeInInches that accepts a
Variant(String) and returns a Single, Double, or Long (as you see fit), then
you could set the OrderBy property to

fncSizeInInches([itemSize])

 

[Tony]

Dirk,

Thanks for the reply. Yes, makes much more sense to pass the values to a
function and do the conversion on the fly (or maybe I can convince my
company to go metric...)

Thanks for the suggestion; I appreciate it.

Ciao

Hi All,

I've got some continuous forms that display information such as:
itemName, itemPrice, itemSize. On each form, when the user clicks the
column heading, the records will sort either ascending or descending,
depending on what the current sort is. For example, if the the current
sort is itemName when the user clicks the itemName heading the sort is
changed to itemName DESC. Everything works. However, when I get to
itemSize, things fall apart because the sizes are in English units
(inches & feet), with a sampling like this:

1-1/2"
6"
10"

If I apply my current sort method and sort by itemSize, the 6" item falls
to the end of the list since the entries are text (they have to be this
way because itemSize units can be inches, feet, or a combination of
both).

I imagine that I could add another column to my table that holds a
conversion of the feet/inces to a decimal, add it as a hidden column on
the form, then use it for sorting. But, I'm wondering if anyone run into
this issue and if so, do you have any tips you can share as to how you
addressed it?

(my first reply to this post seems to have been eaten by the net-news
monsters)

You don't need to create a separate field for this, so long as you can
write
a function that converts the itemSize to a number value in standard units.
For example, if you had a function fncSizeInInches that accepts a
Variant(String) and returns a Single, Double, or Long (as you see fit),
then
you could set the OrderBy property to

fncSizeInInches([itemSize])


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)