Replace Function In A Query

[jo15765]

I have a database that holds a field called PlayaName and in some instances someone input the players number after the name. So sample data would be

Joe Jones
Bob Bills No# 14
Halt Halfway No# 32

My desired output is

Joe Jones
Bob Bills 
Halt Halfway

I tried this syntax, but access gives me an error of the expression you entered has an invalid string.. How should this syntax be written so that if the string contains NO#* then anything after it is stripped?

accuratename: Left(PlayaName, InStr(PlayaName, " (NO# - 1)))

[jo15765]

Okay, after further testing this will work IF the field contains the number, if it does not it throws an error of

#Error
or
#Func!

accuratename: Left([PlayaName],InStr([PlayaName],"NO#")-3)

[ranman256]

Left(PlayaName, InStr(PlayaName, "NO#")-1)

[jo15765]

Left(PlayaName, InStr(PlayaName, "NO#")-1)

That still gives the #Func! if the value does not hold NO#*

[jo15765]

I tried to add in the

 iif()

function but that just completely ignores everything. This syntax is not correct either

iif(PlayaName = "*NO#*", Left([PlayaName],InStr([PlayaName],"NO#")-1), PlayaName)

[aytee111]

IIf(InStr([field1],"NO#")>0,Left([field1],InStr([field1],"NO#")-2),[field1]) AS Expr2

[jo15765]

Ah - I did not know you could check >0 with a text string. Thank you for that!