Sorting by sizes

[dave]

For the life of me I cannot figure out how to sort a view / report by size. Is there a way to do this? Right now I get:

1"
12"
16"
2"
....

Thanks!

[PlantWorker]

Dave,

go to the query behind the list and add a field with follwing name and functon :

sz_sort: Val([PSZ_NOM])

It will show the sizes as numbers, you can keep this field hidden, but use it as a sorter in the query
Only when you have sizes like 1/2" or 1 3/4", it will fail (it returns 1 and 13 ...)

another solution could be to enter your sizes as 001" 002" 003" ....

[dave]

Thanks Plantworker! Worked great. Is there any way to work around the fractions?

[DATC]

We had to do this for the PIPE_RUN table. I was able to do the conversion to numeric as well as substitue decimals for fractions at the same time. The statement used was:

VAL(IIF(LEFT(PIPE_RUN.PSZ,INSTR(PIPE_RUN.PSZ,'""')-1)='1/2','0.5',
IIF(LEFT(PIPE_RUN.PSZ,INSTR(PIPE_RUN.PSZ,'""')-1)='1/4','0.25',
IIF(LEFT(PIPE_RUN.PSZ,INSTR(PIPE_RUN.PSZ,'""')-1)='3/4','0.75',
IIF(LEFT(PIPE_RUN.PSZ,INSTR(PIPE_RUN.PSZ,'""')-1)='1 1/2','1.5',
IIF(LEFT(PIPE_RUN.PSZ,INSTR(PIPE_RUN.PSZ,'""')-1)='3/8','0.375',
LEFT(PIPE_RUN.PSZ,INSTR(PIPE_RUN.PSZ,'""')-1))))))) AS [NUM_SIZE]


This assumes that the only fractions in your table are the common ones;
1/4, 3/8, 1/2, 3/4 and 1 1/2.

CAUTION you may have to convert the decimal sizes back to fractions after sorting to satisfy the project people. :roll:

[DATC]

Just a further note...

The series of quotation marks are: single quote, double quote, double quote, single quote

This sequence is necessary to get the INSTR function to look for the " or double quote (Inch notation) in the text of the data field.