String Stripping

[fabby1]

Hi

I have a series of name e.g.

BRANCHADMIN
none
101315 - P Mayor
101296 - J Smith
101173 - G Tisdell
101082 - S Penny
100895 - L Butler
100827 - P Flemen
100725 - J Ferris
100674 - J Tailford
100671 - N Carrington
100665 - N Hammond
100456 - C Hillhouse
100452 - L Sunter
100440 - J Mokienko

What I want is just to use the Initial and Surname.

I am using a wrapper function called from within the query

Public Function G_Admin(ByVal BranchAdmin As String) As String

If BranchAdmin <> "none" Then
 G_Admin = Right([BranchAdmin], InStr(1, [BranchAdmin], "-") - 1)
End If

End Function

I use the right becuase the staff number may chanmge in length but there will always be "-" so I though work in reverse.

but

I get the following

Name
Tisdell
S Penny
rington
P Mayor
okienko
llhouse
J Smith
Hammond
ailford
 Sunter
 Flemen
 Ferris
 Butler

Any ideas why

Thanks

Phil

 

[cLFlaVA]

Right() takes the right-most x number of characters. You're taking the right-most 8 characters, because the "-" is always the 8th character in the string (from the left).

Use this instead:

[tt]Trim(Mid([BranchAdmin], InStr([BranchAdmin], "-") + 1))[/tt]

*cLFlaVA
----------------------------
Breaking the habit...

 

[fabby1]

Brilliant Works a dream.

But could you explain what it is is doing.

Where does MID get it's start and end values from

Thanks

 

[cLFlaVA]

[tt]Trim(Mid([BranchAdmin], InStr([BranchAdmin], "-") + 1))[/tt]

Trim() - removes spaces from left and right of the string.

Mid() - Takes a substring of the first parameter, starting at the second parameter, and goind for a length of the third parameter. If the third parameter was 1, it would only take one character.

What the line of code above is doing is:

Find the position of "-" in BranchAdmin.
Add 1 to the position (so we don't include the "-" in the new value).
Take a substring of the entire value, starting at the character after "-" and, since we have not specified a length, it takes the rest of the string.
Trim the whitespace.
Return the value you want.

*cLFlaVA
----------------------------
Breaking the habit...