Removing a certain word from a field

[Guest]

Hi.

I have a SchoolName field pulled out in a query, which contains names like:

High St School
Manor Primary School
Finchley First Boys School
Marlborough Secondary

I want to to create another field in the query, called say SchoolNameShort,
which shows the School name minus the word 'School' and the space just in
front of it, for those Schools that have it. It would then show a truncated
name, e.g.

High St
Manor Primary
Finchley First Boys
Marlborough Secondary

Can this be done?

Thanks, JohnB

 

[John Spencer]

Sure, just used a calculated field. If " School" is always at the end,

Field: SchoolShortName: IIF(Right(SchoolName,7)="
School",Left(SchoolName,Len(SchoolName)-7),SchoolName)

Look up Right, Left, Len in VBA help to understand what this is doing.

 

[Guest]

Thanks John.

I'll have to wait til tomorrow to try that out. I'll get back to you then.

Cheers, JohnB

 

[Guest]

Hi again John.

As expected, this works perfectly.

Thanks for the quick and accurate help.

JohnB