Nest Iif using Instr function

[GOVMATE]

Hello,

I am trying to write a query that will search a field for a string until it discovers a comma. If there isn't a comma I want the field left as is. If there is a comma I want it to grab all strings before the comma and then take the string after the comma and flip the arrangement to another field..ie (flipname)

example if a field has [Smith, John] I want it displayed as John Smith

Here's is the code I was attempting to use below, it generates syntax errors!

SELECT Exercise1.name, Iif(Instr[name],",")=0,[Name], Mid([name],Instr([name]),+1,instr([name]),",")-1 as expr1
FROM Exercise1;

Thanks for your help!!!!!!

 

[KeithG]

try this

Iif(Instr[name],",")=0,[Name], Mid([name],Instr([name],",")+1,instr([name],",")-1) as expr1

 

[GOVMATE]

try this

Iif(Instr[name],",")=0,[Name], Mid([name],Instr([name],",")+1,instr([name],",")-1) as expr1

KeithG,

I tried the above and received syntax error missing operator in query expression...

 

[KeithG]

Check out this example. Open query1

 

[boblarson]

Try this instead:

Make sure your field isn't named NAME as that is an Access Reserved Word and that will cause you all sorts of pain.

Here's the formula that will work for you (if you field is changed to tname):

NewName: IIf(InStr(1,[tname],",")=0,[tname],Mid([tname],InStr(1,[tname],",")+1) & " " & Left([tname],InStr(1,[tname],",")-1))

 

[raskew]

Hi -

It appears to me that you're not picking up the last name correctly. Try this:

 IIf(InStr([name],',')=0,[name],Mid([name],InStr([name],',')+1))  & Left([name],InStr([name],',')-1) AS Expr1

Bob

 

[boblarson]

Hi -

It appears to me that you're not picking up the last name correctly. Try this:

 IIf(InStr([name],',')=0,[name],Mid([name],InStr([name],',')+1))  & Left([name],InStr([name],',')-1) AS Expr1

Bob

Who are you addressing this to Bob. My code

NewName: IIf(InStr(1,[tname],",")=0,[tname],Mid([tname],InStr(1,[tname],",")+1) & " " & Left([tname],InStr(1,[tname],",")-1))

works perfectly for both first and last name as they wanted it to show the full name (I know it does because I actually tested it). If you choose yours, or Keith's, it doesn't show the whole name right.

 

[raskew]

boblarson -

Sorry. I was referring to the initial posts. My response slid in a few seconds after yours.

Bob

 

[GOVMATE]

I appreciate you guys helping!!!!

 

[boblarson]

I trust you got it to the way you want it.

 

[uatkd1]

Try this instead:

Make sure your field isn't named NAME as that is an Access Reserved Word and that will cause you all sorts of pain.

Here's the formula that will work for you (if you field is changed to tname):

NewName: IIf(InStr(1,[tname],",")=0,[tname],Mid([tname],InStr(1,[tname],",")+1) & " " & Left([tname],InStr(1,[tname],",")-1))

Thank you so much for this! I searched high and low for help on this...thank you again