RE: Syntax error in qery SQL when using where and like

LillyC
LillyC used Ask the Experts™
on
Hi,
I am trying to create new field in query that removes letter F fro the end of string.  The letter appears only in some records.  I have tried below but get a syntax error message:

SPNew: Left([Rstr],Len([Rstr])-1) where [Rstr] like '*F'

where am I going wrong with syntax?

Thank you
LillyC
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
You need an expression like:

SPNew: Iif([Rstr] Like '*F', Left([Rstr],Len([Rstr])-1), [Rstr])

Open in new window

Try this:
SPNew: IIf( [Rstr] like '*F', Left([Rstr], Len([Rstr])-1), [Rstr])

Ron
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
How about this:

SPNew: IIF (Right([Rstr],1)="F", Replace (Right([Rstr],1),"F",""), [Rstr])
LillyCDatabase Developer

Author

Commented:
Perfect, thanks  :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial