sorting by everything after last space

[Abby Lee]

I need to sort my query by group.
Table created from electronic feed. User filed is made up of Division (320)
/ Person / Group
examples:
320 Hurst, T ADM
320 KERNER C PI
320 808 ACCESS TIP
320 POURMAN E MICS

How do I sort my query by everything after the last space?

 

[John Spencer]

What version of Access?

You can try sorting by the following expression

Mid([TheField],1+InstrRev([TheField]," ",-1,1))

If you get an unrecognized function error, then post back and tell us your
version of Access.

 

[TedMi]

Recording multiple facts in a single field is a violation of relational
database principles. You should split the field into several, each
identifying a single entity. Then you can sort on any one of these or a
combination.

 

[John Vinson]

I need to sort my query by group.
Table created from electronic feed. User filed is made up of Division (320)
/ Person / Group
examples:
320 Hurst, T ADM
320 KERNER C PI
320 808 ACCESS TIP
320 POURMAN E MICS

How do I sort my query by everything after the last space?

Put in a calculated field:

Group: Mid([Userfield], InStrRev([Userfield], " ") + 1)

and sort by it.

This will work in (I believe) A2002 and later - the InStrRev function
was not present in older versions. Post back if that's a problem.

Any chance you could permanently parse this out into three fields?
Sorting by an unindexed substring is going to be dog slow if your
table is of any great size!

John W. Vinson[MVP]