How can I make the code understand that when sorting something by size for example, that 8" in small than 10"?

When I use:

rs.Open "SELECT * FROM plant_list WHERE grp_name = '" & request.QueryString("group") & "' and available = True ORDER BY name, pot_size", conn

I sorts it as 10" before 8". I am assuming this is because "1" comes before "8"?

How can I work around it without using a leading zero?

Thanks for any help.

did u try adding asc to the end?

ORDER BY name, pot_size asc", conn

If you do not add "ASC" the results will be sorted by ascent order. Since there are two fields in your "ORDER BY" clause, the first sort will be based on the field "name" and then "pot_size".

i had a similar sorting problem wiht my asp page.

for me - i fixed it by changing the sroting column fortm 1,2,3, to 01,02,03

I assume POT_SIZE is holding your 10 and 8 values.

If Pot_size is a char or varchar, then 10 will always come first.

If Pot_size is a numeric field of some sort, then 8 will always come first.

`Le

pot_size is holding the values 8" and 10" so it is varchar.

It has to hold the " (for inches) or the ' (for feet). I cannot store just the number itself without the " or '.

Any ideas? Thanks for the help.

There is not much you can do here I am afraid. I would suggest you convert it to integer when you do the Select, but that will not help since you store Feet AND inches in the same field.

For example, you could remove the ' and '', but that means 10'5'' would convert to 105, which makes no sense.

If the field ONLY kept inches or ONLY kept feet, then it would work. But as such, there is no way I can think of to get the values you need.

Sorry. This is just another reason we should be in the metric system.

Ultimately the best solution is to covert all the data to inches and store it is a numeric or integer.

Sorry, but it looks like you are out of luck on this one.

`Le

thele,

I appreciate your feedback. It gave me a couple of ideas. I am going to recommend converting all sizes to inches, and storing just the integer in the db and adding the " to the display.

Or I might go through alot of work, and store the " and ' in a separate field, thus stoing the integer by itself, and joining them later in the display. Of course I am hoping the client picks the former. (wishful thinking!)

Thanks for your comments!