Left () code issue, can it ignore anything thats not a letter?

[Chrisopia]

Switch(IsNull([CompanyName]),UCase(Left([LastName],3)),Not IsNull([CompanyName]),UCase(Left([CompanyName],3)))

I'm using a switch statement to decipher the left 3 characters of the Company name or Last Name, then upper casing them...

this is all very well, but every so often I'll have a company called:
AB C ltd, which would output as AB

and AB-C ltd would output as AB-

and so on, this 3 letter code runs through a count qry which will add a number to the end to create a customer reference code, so I also need it to ignore numbers,

is there a Left() code designed just for letters?

 

[wazz]

maybe the IsNumeric function can help. i guess you'll have to check every character.

 

[John.Woody]

You could modify this function

Public Function FilterIt(InChr As String) As String
'-- Strip all but alphanumeric characters
   Select Case InChr
      Case "a" To "z"
         FilterIt = InChr     '-- Valid character
      Case "A" To "Z"
         FilterIt = InChr     '-- Valid character
      Case "0" To "9"
         FilterIt = InChr     '-- Valid character
      Case Else
         FilterIt = ""        '-- Strip this character
   End Select
End Function

Change

Case "0" To "9"
FilterIt = ""        '-- Strip this character

and that should filter alpha only

hth