sorting by everything after last space

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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.
 
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?
Click to expand...

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]
 
Back
Top