Is there a better way to write this expression?

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,297
Office Version
  1. 365
Platform
  1. Windows
I'm using this expression in a query:
Code:
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
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. :biggrin:
 
Upvote 0
LOL!! ...here's the "avoid an ear bashing from your Boss" solution:

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

hth,
Giacomo
 
Upvote 0

Forum statistics

Threads
1,217,481
Messages
6,136,903
Members
450,029
Latest member
MissQuotation

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top