Remove Last 3 Characters from field - results in invalid procedure error

[lefty38]

in a query I am trying to remove the last 3 characters in a result set

NewField: LEFT([JOB_TITLE],LEN([JOB_TITLE])-3)

but keeps on getting "Invalid Procedure Call"

is it the expression above ? or is it something else?

 

[MUKESHY12390]

it's seems you tried with table.

try like this ..

 =LEFT(Table1[[#This Row],[FilmNAme]],LEN(Table1[[#This Row],[FilmNAme]])-3)

 

[MUKESHY12390]

it's seems you tried with table.

try like this ..

 =LEFT(Table1[[#This Row],[FilmNAme]],LEN(Table1[[#This Row],[FilmNAme]])-3)

my bad I thought it was for excel table.

I think you should include table name as well like this..Expr1: Left([customers].[Company],Len([customers].[Company])-3)

 

[lefty38]

I did, but I edited it for the post

JobTitle: LEFT(

.[JOB_TITLE],LEN(

.[JOB_TITLE])-3)

i will double check tommorrow at work

 

[james_lankford]

are you sure

.[JOB_TITLE] is always longer than 3 characters ?

that should fail if its shorter than 4 or if its blank or if its null

 

[lefty38]

I will check when I get back to work
it seems to process then fails when data is returning

 

[lefty38]

All - yes there was a value with a blank cell

so I guess how do you convert this expression to check for and exclude null values?

JobTitle: LEFT(

.[JOB_TITLE],LEN(

.[JOB_TITLE])-3)

 

[Joe4]

You can use an IIF statement to check to see if the length is greater than 3, i.e.

[COLOR=#333333]IIF([/COLOR][COLOR=#333333]LEN([table].[JOB_TITLE])>3,[/COLOR][COLOR=#333333]LEFT([table].[JOB_TITLE],LEN([table].[JOB_TITLE])-3,"")[/COLOR]

 

[xenou]

IIF() might still fail because IIF() evaluates all arguments (no short-circuit evaluation).

Lacking context, possibly (using three spaces between the quotes):
Trim(Left("   " & [Job_Title], 3))

Or perhaps if you are able, create and use job title ID as a way to keep the data cleaner.

Note: Disregard. Bleh.

-----

Okay, meant this:
Trim(Left([Job_Title] & "   ", 3))