Is there a better way to write this expression?

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
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:
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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
 

Forum statistics

Threads
1,137,340
Messages
5,680,916
Members
419,945
Latest member
Carrie Sellers

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
Top