Re: Sorting Access text fields?

> Suppose I have an Access database table with a text field
> that has the following data: (B1, B2, B5, B8, B11, B70, B170).
> I want them sorted exactly in that order.  However, because
> this is a text field, it doesn't sort them that way at all.

hi mike

your hunch was good, it can be done with sql functions

  select  Left(foo,1)
          +  Right(Format(Val(Mid(foo,2)+100000000)),3) as foo2
     from yourtable
  order by 1

the order by number is the number of the column, i.e. 1 since there's only
one column in the result set

you can't say order by foo2 (the alias of the column expression), or at
least you couldn't in access 97

foo2 is a text field that is 4 characters in length, the first original
character plus three digits for the original number, right-aligned in a
3-character field with zeros filled in (you can increase this by increasing
the number 3 in the Right() function, up to 8, at which point you also have
to change the number 100000000)

the plus sign is the way you concatenate strings in microsoft access


everybody likes to pooh-pooh microsoft access by i think it's a darned good
database, even if its sql syntax is way out in left field.....


rudy
http://rudy.ca/

HWG hwg-techniques mailing list archives, maintained by Webmasters @ IWA