Sorting Dimensions

I have created a small database to list materials by dimension and price. I have a text field for the material sizes, i.e. 1 1/2x 1v 1/2 x 1/2.

My problem is that it sorts the fractions incorrectly. I am trying to sort the sizes in ascending order but it will sort as follows, 1/2 then 1/4 then 1/8 in that order which for my purposes need to be sorted 1/4 then 1/2 then 1/8 because these are material thicknesses and incrementally increase in this order.

Access is seeing each digit individually instead of as a fraction. therefore the 2 in 1/2 comes before the 4 in 1/4.

I hope I have explained this clearly, if not ask questions and I will try to clarify the problem.

My request? How do I sort the numbers as fractions? If I change the field to a number field then I cannot place the "x" between the numbers to demonstrate that they are material dimensions.

Help? Thanks!

Bill Stum

First, wouldn't it be...

1/8, then 1/4, then 1/2?


Secondly, this field sounds like it is a text field. You have a couple of
options. If the first part of your fraction is always 1, then simply sort
by the digits folowing the '/'. Another option (the one I'd use) is to
store this in a number field as a decimal.

You couls still place the 'x' but these fields would be three separate
field.


Field1 named length, Field 2 named width, Field 3 named depth.

Then to display...

[Width] & " x" & [Height] & " x" & [Depth]




Rick B


"wfs1946" wrote in message
...
I have created a small database to list materials by dimension and price. I
have a text field for the material sizes, i.e. 1 1/2x 1v 1/2 x 1/2.

My problem is that it sorts the fractions incorrectly. I am trying to sort
the sizes in ascending order but it will sort as follows, 1/2 then 1/4 then
1/8 in that order which for my purposes need to be sorted 1/4 then 1/2 then
1/8 because these are material thicknesses and incrementally increase in
this order.

Access is seeing each digit individually instead of as a fraction. therefore
the 2 in 1/2 comes before the 4 in 1/4.

I hope I have explained this clearly, if not ask questions and I will try to
clarify the problem.

My request? How do I sort the numbers as fractions? If I change the field
to a number field then I cannot place the "x" between the numbers to
demonstrate that they are material dimensions.

Help? Thanks!

Bill Stum

Rick,

Thanks for the math lesson, it's been one of those days, but you did get the idea of what I'm trying to do.

I thought about the decimal idea but the people I'm dealing with can't handle higher math, I don't mean to pick on anyone but if I throw decimal numbers at them they will have a worse time sorting them in their minds than I did with the fractions.

You are right about it being a text field. If I use three separate fields how do I tie them together to make sure I keep the right three dimensions together? Another factor is that not all the metals have three dimensions, an example would be solid stock that is 2" x 2" with no wall thickness.

I guess my next question would be is there any way to make fractions show as true fractions in Access, I know that some do in Word but I have not been able to find anything similar in Access.

Thanks again for your reply,
Bill

You might want to repost this and get some help from an MVP. I think
putting them in one big field is a problem. I would say that you should
have the three fields. When you create your record, you can enter the
values into each field. (Even if you want to enter them as text and
continue with the current format). Use an input mask to force the "/" in
there and help the user enter it properly.

Don't make height required. Then the user could enter two dimensions, or
three.

Use the method I mentioned to string them together for forms or reports, but
include an if statement to exclude the height dimension and the "x" if there
is no height...

=[Length] & " x " & [Width] & IIf([Height]," x " & [Height],"")

Now, I guess my biggest issue goes back to sorting. If you always list
length x width x height, then the sorting issue is no longer important. It
is possible that I misunderstood your post, but it seems to me that 1/4 x
1/8 x 1/2 is accurate assuming the length is 1/4, the width is 1/8, and the
height is 1/2. I can't see why you'd want to move the numbers around into
1/8 x 1/4 x 1/2.

Again, I may have misunderstood that part of the question.

Hope some of this helps,

Rick B






"wfs1946" wrote in message
...
Rick,

Thanks for the math lesson, it's been one of those days, but you did get the
idea of what I'm trying to do.

I thought about the decimal idea but the people I'm dealing with can't
handle higher math, I don't mean to pick on anyone but if I throw decimal
numbers at them they will have a worse time sorting them in their minds than
I did with the fractions.

You are right about it being a text field. If I use three separate fields
how do I tie them together to make sure I keep the right three dimensions
together? Another factor is that not all the metals have three dimensions,
an example would be solid stock that is 2" x 2" with no wall thickness.

I guess my next question would be is there any way to make fractions show as
true fractions in Access, I know that some do in Word but I have not been
able to find anything similar in Access.

Thanks again for your reply,
Bill

Rick,

Let me try to clarify. I have multiple materials with varying dimensions, let's use one dimension for simplicity. I have three pieces of metal one 1/4 thick, one 1/2 thick and one 1/8 thick. I need to be able to sort in ascending order according to thickness. With this is mind it should sort with the order of 1/8 then 1/4 then 1/2, thinnest to thickest, but.....access sees the number after the "/" as a separate digit and not as part of the fraction therefore it sorts 2 then 4 then 8 which means my thicknesses sort 1/2 then 1/4 then 1/8 which is not correct from thinnest to thickest dimension.

thanks again,
Bill

I think you will have to evaluate each fraction into a decimal number, and
sort on that, else you will have endless trouble parsing things like 5 3/4
inches.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
"wfs1946" wrote in message
...
Rick,

Let me try to clarify. I have multiple materials with varying dimensions,
let's use one dimension for simplicity. I have three pieces of metal one
1/4 thick, one 1/2 thick and one 1/8 thick. I need to be able to sort in
ascending order according to thickness. With this is mind it should sort
with the order of 1/8 then 1/4 then 1/2, thinnest to thickest,
but.....access sees the number after the "/" as a separate digit and not as
part of the fraction therefore it sorts 2 then 4 then 8 which means my
thicknesses sort 1/2 then 1/4 then 1/8 which is not correct from thinnest to
thickest dimension.

thanks again,
Bill

As Rick said, that's because it's a text field. Access doesn't realize 1/8
is supposed to be a fraction: it just sees it as 3 characters. In other
words, it sees 3 strings "1/4", "1/2" and "1/8". To it, the first 2
characters are the same in each, and so it sorts on the third character: 2,
4, 8.

There's no way to get Access to sort any differently by itself. You could
write a function that converts the string 1/4 to .25, 1/2 to .5 and so on,
or you could have a table of fractions which you use, and have a sort order
defined in that table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"wfs1946" wrote in message
...
Rick,

Let me try to clarify. I have multiple materials with varying dimensions,
let's use one dimension for simplicity. I have three pieces of metal one
1/4 thick, one 1/2 thick and one 1/8 thick. I need to be able to sort in
ascending order according to thickness. With this is mind it should sort
with the order of 1/8 then 1/4 then 1/2, thinnest to thickest,
but.....access sees the number after the "/" as a separate digit and not as
part of the fraction therefore it sorts 2 then 4 then 8 which means my
thicknesses sort 1/2 then 1/4 then 1/8 which is not correct from thinnest to
thickest dimension.

thanks again,
Bill

I want to thank everyone for all their suggestions. I now have a sense of direction as to what needs to be done, being a novice we will see how long it takes me to get there.

Thanks again everyone.
Bill