Left 2 text digits of alphanumeric number

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

Access 2000. I have been trying to get a query to return the first,
second, or third letters of an order number. Ive used : left([Cert_ID],2)
etc but it will also return numbers where I only want text returned.
Example I have order numbers such as: A123232, CD32323, and ABD4343 I only
want the first, second or third text returned on my query. I need to see:
A or CD or ABD. Thanks in advance.
 
Access 2000. I have been trying to get a query to return the first,
second, or third letters of an order number. Ive used : left([Cert_ID],2)
etc but it will also return numbers where I only want text returned.
Example I have order numbers such as: A123232, CD32323, and ABD4343 I only
want the first, second or third text returned on my query. I need to see:
A or CD or ABD. Thanks in advance.
Click to expand...

Copy and Paste the below function into a Module:

Public Function GetAlphas(strText)
If IsNull(strText) Then
Exit Function
Else
Dim intZ As Integer
Dim intY As Integer
Dim intX As Integer
intX = Len(strText)
Dim TheAlpha As String
For intZ = 1 To intX
intY = Asc(Mid(strText, intZ))
If intY >= 48 And intY <= 57 Then
Exit For
Else
TheAlpha = TheAlpha & Chr(intY)
End If
Next intZ
End If
GetAlphas = TheAlpha
End Function

Then you can call it from a query:

OnlyText:GetAlphas([Cert_ID])
 
Use one of the following MSAccess specific queries as a model"

SELECT
iif(not isnumeric(Mid(Cert_ID,3,1)), left(Cert_ID,3),
iif(not isnumeric(Mid(Cert_ID,2,1)), left(Cert_ID,2),
Left(Cert_ID,1)))
AS Ltrs
FROM TableName;


SELECT
left(Cert_ID,1)+
IIf(isnumeric(mid(Cert_ID,2,1)),"",mid(Cert_ID,2,1))+
IIf(isnumeric(mid(Cert_ID,3,1)),"",mid(Cert_ID,3,1))
AS Ltrs
FROM TableName;

Brian
 
Thank you very much. Works great.

fredg said:
Access 2000. I have been trying to get a query to return the first,
second, or third letters of an order number. Ive used :
left([Cert_ID],2)
etc but it will also return numbers where I only want text returned.
Example I have order numbers such as: A123232, CD32323, and ABD4343 I
only
want the first, second or third text returned on my query. I need to
see:
A or CD or ABD. Thanks in advance.
Click to expand...

Copy and Paste the below function into a Module:

Public Function GetAlphas(strText)
If IsNull(strText) Then
Exit Function
Else
Dim intZ As Integer
Dim intY As Integer
Dim intX As Integer
intX = Len(strText)
Dim TheAlpha As String
For intZ = 1 To intX
intY = Asc(Mid(strText, intZ))
If intY >= 48 And intY <= 57 Then
Exit For
Else
TheAlpha = TheAlpha & Chr(intY)
End If
Next intZ
End If
GetAlphas = TheAlpha
End Function

Then you can call it from a query:

OnlyText:GetAlphas([Cert_ID])
Click to expand...
 
Back
Top