dropping the employee number from an entry

[Jon123]

I'm able to pull users that are logged into our network but our company uses a name that has their employee number as well. So deponding on when they were hired it could be a 5 or 6 digit number. so an example would be
JSmith123456 or it could be JSmith12345 so is there a way to drop the numbers at the end? Meaning if they are entering data into a form that pulls their name can I drop off those last digits in the name field?

jon

 

[jdraw]

I can see the company having an EmployeeCode or EmployeeID as an alphanumeric as you have shown. But I would expect that field to be the PK of the Employee record that would include EmployeeFirstName, EmployeeLastName and other Employee specific information.

You can then create a query to use the EmployeeCode/ID to get the EmployeeName (First and Last).

 

[Mr. B]

Here is a function that will return just the alpha characters in the string provided to it:

Public Function GetTextOnly(sLoginName) As String
Dim varFirstNumLoc, cntr

For cntr = 1 To Len(sLoginName)
    If IsNumeric(Mid(sLoginName, cntr, 1)) Then
        varFirstNumLoc = cntr
        GetTextOnly = Left(sLoginName, (cntr - 1))
        Exit Function
    End If
Next cntr
End Function

Hope this helps.

 

[MarkK]

Here's an approach that (optionally) starts five characters from the end and works its way forward . . .

Function DropTrailingNumbers(Text As String, Optional DropChars As Integer = 5) As String
    Do While IsNumeric(Mid(Text, Len(Text) - DropChars))
        DropChars = DropChars + 1
    Loop
    DropTrailingNumbers = Left(Text, Len(Text) - DropChars)
End Function

 

[Jon123]

So how do I call these functions?

 

[MarkK]

I'm able to pull users that are logged into our network

What does that mean, you are able to pull users? Post your "pulling" code or query or whatever, and we'll work it in.

 

[Jon123]

I have the control source of a field set to
=GetWinUserName()
this returns the user name logged into the pc.

 

[Jon123]

But I cant figure out how to drop the numbers following the name??????

 

[Jon123]

thank you guys got it working great