Conditionally Trimming Lead Chars Of Value In Query?

[PeteCresswell]

Got a query that returns account names for a dropdown.

Many accounts begin with "SEI ". e.g. "SEI Global Lending", "SEI
Liquidity"....

User to lose the redundant prefixes, yet not change account names.

Tried this within the query, but it generated a circular reference:

TradingAccountName: IIf(Left$([TradingAccountName],4)="SEI ",Replace
([TradingAccountName],"SEI ",""),[TradingAccountName])

Anybody know the Good-Right-And-Holy Path?

 

[Douglas J. Steele]

TradingAccountName: IIf(Left$([NameOfTable].[TradingAccountName],4)="SEI
",Replace([NameOfTable].[TradingAccountName],"SEI
",""),[NameOfTable].[TradingAccountName])

 

[Clif McIrvin]

Pete, thank you! for asking this question!

Beacuse you did, I'll be able to use Doug's answer to clean up a similar
mess of my own.

I was actually nesting queries ... one to massage the value, and one to
restore the expected field name. <shudder>

Thanks again to both of you!

--
Clif

TradingAccountName:
IIf(Left$([NameOfTable].[TradingAccountName],4)="SEI
",Replace([NameOfTable].[TradingAccountName],"SEI
",""),[NameOfTable].[TradingAccountName])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Got a query that returns account names for a dropdown.

Many accounts begin with "SEI ". e.g. "SEI Global Lending", "SEI
Liquidity"....

User to lose the redundant prefixes, yet not change account names.

Tried this within the query, but it generated a circular reference:

TradingAccountName: IIf(Left$([TradingAccountName],4)="SEI ",Replace
([TradingAccountName],"SEI ",""),[TradingAccountName])

Anybody know the Good-Right-And-Holy Path?