A query that ignores number characters and only returns text

[keun]

I need to rip the last name out of a username field. The username is formatted First Initial, Last Name, and an incrementing number for uniqueness. eg:

USERNAME
jsmith
jjones
jsmith1
rlove

I am really close with:

Last: Right([username],Len([username])-1)

But I need to strip the number off the end of any usernames that have a number. I am thinking the best way is a query that ignores number characters. Any thoughts?

I joined this forum in 2005. I am still a hack.

 

[dhookom]

I believe you can use the following expression based on your sample values. If there are two or more numbers this won't work.

Left([USERNAME],Len([USERNAME]) + Isnumeric(Right([USERNAME],1)))

Duane
Hook'D on Access
MS Access MVP

 

[keun]

Unfortunately, I cannot be sure that I can assume only one digit. So maybe:

USERNAME
jsmith
jjones
jsmith1
rlove
jsmith909
rjohanssen42

Is there a way I can start at the left an then look for the first instance of 1-9 and then base my query on everything to the left of that position?

I joined this forum in 2005. I am still a hack.

 

[MajP]

Select RemoveNumbers([SomeField]) as CleanName from some table

Public Function RemoveNumbers(FieldValue As Variant) As String
  Dim I As Integer
  If Not IsNull(FieldValue) Then
    If IsNumeric(Right(FieldValue, 1)) Then
      For I = 0 To 9
        FieldValue = Replace(FieldValue, I, "")
      Next I
      RemoveNumbers = FieldValue
    End If
  End If
End Function

 

[dhookom]

I would use a function like MajP suggests however I think one line needs to be added to return the correct value if there are no numbers:

Public Function RemoveNumbers(FieldValue As Variant) As String
  Dim I As Integer
  If Not IsNull(FieldValue) Then
    [COLOR=#A40000]RemoveNumbers = FieldValue[/color]
    If IsNumeric(Right(FieldValue, 1)) Then
      For I = 0 To 9
        FieldValue = Replace(FieldValue, I, "")
      Next I
      RemoveNumbers = FieldValue
    End If
  End If
End Function

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Thanks Duane. Good catch

Or you may simply leave the return as a variant, so a null would return as a null instead of an empty string

Public Function RemoveNumbers(FieldValue As Variant) As variant
  Dim I As Integer
  If Not IsNull(FieldValue) Then
    If IsNumeric(Right(FieldValue, 1)) Then
      For I = 0 To 9
        FieldValue = Replace(FieldValue, I, "")
      Next I
    End If
  End If
  RemoveNumbers = fieldValue
End Function

For any function used in a query i always make the parameters variants and then handle an empty field.

 

[keun]

Thanks, that helps. As it was I was manually QCing for NULLs.

I joined this forum in 2005. I am still a hack.