Conditionally Trimming Lead Chars Of Value In Query?

P

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?
 
D

Douglas J. Steele

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

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

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


PeteCresswell said:
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?
Click to expand...
Click to expand...
 
Top