Access - How do I sort by only the first 4 characters of a field?

[Guest]

 

[fredg]

If you have a question about Access databases, this big white space in
the message is where you should ask it. An example would be helpful.

 

[Guest]

On the advanced filter/sort page I want to sort all fields ascending by the
first field, then the second, then the third, then the fourth. I know how to
do that but when the sorting engine looks at the second field I only want it
to sort by the first 4 characters and not the first 255 which is the default.
The reason being I have data like this

drama movie title
drama / romance movie title
drama / crime movie title
drama / mystery movie title

I don't want it to sort "drama" then alphabetical movie titles, then "drama
/ romance" then alphabetical movie titles etc.

I have looked at the help menu and it has something to do with putting a
formula in the criteria box. I have found a similar formula but it does not
do exactly what I want.

 

[RoyVidar]

On the advanced filter/sort page I want to sort all fields ascending
by the first field, then the second, then the third, then the
fourth. I know how to do that but when the sorting engine looks at
the second field I only want it to sort by the first 4 characters
and not the first 255 which is the default. The reason being I have
data like this

drama movie title
drama / romance movie title
drama / crime movie title
drama / mystery movie title

I don't want it to sort "drama" then alphabetical movie titles, then
"drama / romance" then alphabetical movie titles etc.

I have looked at the help menu and it has something to do with
putting a formula in the criteria box. I have found a similar
formula but it does not do exactly what I want.

In the SQL view of the table, the order by clause might look something
along the lines of this

ORDER BY field1, Left(field2, 4), field3, field4

 

[Rick B]

The best way to do this would be to normalize your design. You should have
a category (drama) and a subcategory (romance) field.

You can then sort and group nicely.

 

[John Vinson]

On Mon, 7 Aug 2006 22:25:01 -0700, Glenn

Put a calculated field in the Query

SortBy: Left([fieldname], 4)

and sort by it.

The records will also be sorted by the left four characters of the
field if you just sort by the field; with the above sort, the order of
records after the fourth character is undefined and unpredictable,
whereas if you sort by the field itself, the order will be
alphabetical. Not sure why you want an unpredictable sort in
preference to an orderly one!

John W. Vinson[MVP]