Is there a better way to write this expression?

[Darren Bartrup]

I'm using this expression in a query:

Specialism: IIf(Left([tblTeamManagement]![fldTeamName],12)="Older People","OP",IIf(Left([tblTeamManagement]![fldTeamName],2)="OT","IL",IIf(Left([tblTeamManagement]![fldTeamName],3)="PSI","IL",[tblTeamManagement]![fldTeamName])))

In English:
If the first 12 letters of fldTeamName is Older People, then put OP
Else
If the first 2 letters of fldTeamName is OT or the first 3 letters of fldTeamName is PSI then put OT
Else
put fldTeamName

Is there a better or shorter way of putting this?
Any help is greatly appreciated.

 

[giacomo]

I'm not a big fan of putting business logic into my queries. I think it's better to create a table with a Specialism for each fldTeamName.

hth,
Giacomo

 

[Darren Bartrup]

Thanks for the reply giacomo.

Your suggestion is the obvious way to go, but (and there's always a but) the team name list is automatically updated from a spreadsheet. With abit of work (and a big frown from my boss 'cos I'm still working on this project) I could add a specialism choice onto the form that pops up in excel when a new team is found.

Hmmm.... maybe. I've got an tickbox that gives the user the choice to ignore the team so it wouldn't be much to change it to a combo-box with OP, IL or Ignore in it.

**** you giacomo!!!! Now you've got me thinking of better ways to do it and I'm going to end up doing it that way which means an ear-bashing from my boss for taking too long.

 

[giacomo]

LOL!! ...here's the "avoid an ear bashing from your Boss" solution:

Specialism: IIf( [tblTeamManagement]![fldTeamName] like "Older People*","OP", IIf( tblTeamManagement]![fldTeamName] like "OT*" OR [tblTeamManagement]![fldTeamName] like "PSI*","OT",[tblTeamManagement]![fldTeamName]))

hth,
Giacomo

 

[Darren Bartrup]

Thankyou

Have a Jim'll Fix It badge.