Instr function

[GOVMATE]

I'm examining a previously written query and I'm trying to figure out exactly what the minus sign does when placed before the Instr function. An example of a query that successfully flips a name field is below and includes the -instr function. I've also included another query below this one that is much more simple and does the same thing. Thanks in advance for your help!!!


1)
EXERCISE1 SET EXERCISE1.NAME_A = IIf(InStr([Name],",")=0,[Name],IIf(InStr([Name],",")=InStrRev([Name],","),Trim(Mid([Name],InStr([Name],",")+2,20)) & " " & Mid([Name],1,InStr([Name],",")-1),Mid([Name],InStr([Name],",")+2,(InStrRev([Name],",")-InStr([Name],",")-2)) & " " & Mid([Name],1,InStr([Name],",")-1)));



2)
SELECT EXERCISE1.NAME, IIf(InStr([NAME],",")=0,[NAME],Trim(Mid([name],InStr([NAME],",")+1)) & " " & Mid([NAME],1,InStr([NAME],",")-1)) AS FLIPPEDNAME
FROM EXERCISE1;

 

[boblarson]

It isn't the -instr but the minus sign in the examples you showed is just telling the Mid function to return the string up to the point where the character is found, but minus 1 character so that the comma doesn't get included in the string. The same goes with the plus (+) sign. It is telling it not to include the number of characters after the comma is found.

 

[Brianwarnock]

Bob I think its this one that he is referring to
Mid([Name],InStr([Name],",")+2,(InStrRev([Name],",")-InStr([Name],",")-2))

I think its that the length is from the 1st comma -to the last comma -2
ie if [name was (fred,williams,bloggs) the length would be 14-5-2=7 which doesn't seem correct so maybe I got that wrong.

Brian

Having checked on InStrRev it says that it gives the position from the end of the string which means we have 7-5-2 in my example. I'm confused.