Stripping out Alpha from a Field

  • Thread starter Thread starter Scott Duncan
  • Start date Start date
S

Scott Duncan

We have Part Numbers that are Alpha Numeric...

They are based on a Numeric Base Part Number. Alpha Characters, both
Prefixes and Suffixes
indicate Revision & Special Finish.

In my Query, I want to strip out all Alpha Characters from the Part Number
to reveal the Base Part Number.

Base: Val([Part Number]) does not seem to work,
nor does CLng([Part Number]).

Beating my head into my desk...

Any help would be appreciated.

SD
 
If you will give us some representative examples of your Part Numbers,
perhaps we can come up with a solution.
 
Cheryl ,

Thank you for the reply...

Some Examples are:
01721200A
A01721200A
B01721200A
A017212R0A
B017212R0A

[Revision] + [Base Number] + [Finish]
A 017212 00A

Now, "most" Part Numbers are 9 Characters A/N
When there is a change with the part, a Revision will be prefixed.
There are also some part numbers that will have multiple [Alpha] characters
in the prefix or suffix.

All Base Part Numbers [for now] are Six (6) Digits, no Alpha.

Thank you again,

SD

Cheryl Fischer said:
If you will give us some representative examples of your Part Numbers,
perhaps we can come up with a solution.

--

Cheryl Fischer, MVP Microsoft Access



Scott Duncan said:
We have Part Numbers that are Alpha Numeric...

They are based on a Numeric Base Part Number. Alpha Characters, both
Prefixes and Suffixes
indicate Revision & Special Finish.

In my Query, I want to strip out all Alpha Characters from the Part Number
to reveal the Base Part Number.

Base: Val([Part Number]) does not seem to work,
nor does CLng([Part Number]).

Beating my head into my desk...

Any help would be appreciated.

SD
Click to expand...
Click to expand...
 
There are really three fields which have been concatenated into one here:
RevisionPrefix, BasePartNum, RevisionSuffix; and, has been said many times
in these newsgroups, it is ALWAYS easier to concatenate multiple fields
together to display in a form or report than it is to try to split
concatenated data.

Anyway, you will need a function to test for the presence of a number in
each character of the PartNumber, when the function finds the *first*
character between 0 and 9, it presumes that that character is the first
digit of the Base Part Number. The function then returns a 6-character
string beginning with that character.

You can use the function in a query by entering the following in the first
empty column in your query:

BasePart: GetBasePart([PartNumber])

' Copy the following function to a module
Public Function GetBasePart(strPartNum As String) As String

Dim i As Integer

If Left(strPartNum, 1) >= 0 And Left(strPartNum, 1) <= 9 Then
GetBasePart = Left(strPartNum, 6)
Exit Function
End If

For i = 2 To Len(Trim(strPartNum))
If Mid(strPartNum, i, 1) >= 0 And Mid(strPartNum, i, 1) <= 9 Then
GetBasePart = Mid(strPartNum, i, 6)
Exit Function
End If
Next i

End Function

--

Cheryl Fischer, MVP Microsoft Access



Scott Duncan said:
Cheryl ,

Thank you for the reply...

Some Examples are:
01721200A
A01721200A
B01721200A
A017212R0A
B017212R0A

[Revision] + [Base Number] + [Finish]
A 017212 00A

Now, "most" Part Numbers are 9 Characters A/N
When there is a change with the part, a Revision will be prefixed.
There are also some part numbers that will have multiple [Alpha] characters
in the prefix or suffix.

All Base Part Numbers [for now] are Six (6) Digits, no Alpha.

Thank you again,

SD

Cheryl Fischer said:
If you will give us some representative examples of your Part Numbers,
perhaps we can come up with a solution.

--

Cheryl Fischer, MVP Microsoft Access



Scott Duncan said:
We have Part Numbers that are Alpha Numeric...

They are based on a Numeric Base Part Number. Alpha Characters, both
Prefixes and Suffixes
indicate Revision & Special Finish.

In my Query, I want to strip out all Alpha Characters from the Part Number
to reveal the Base Part Number.

Base: Val([Part Number]) does not seem to work,
nor does CLng([Part Number]).

Beating my head into my desk...

Any help would be appreciated.

SD
Click to expand...
Click to expand...
Click to expand...
 
Cheryl,

Thank you very much...

I understand the implications of the of this mess... There is not even any
common ground for
a 100% bullet proof Parcing routine...

This is what I had been working with. Works fine in a Form environment.

Public Function StripAlpha(InputString) As String
On Error GoTo Err_StripAlpha

Dim I%, Temp, T
Dim Out As Control
Set Out = Screen.ActiveControl
I% = Len(InputString)
'========= Strip Out Alpha Characters ==========
For N = 1 To Len(InputString)
T = Mid$(InputString, N, 1)
If InStr("1234567890", T) > 0 Then
Temp = Temp + T
End If
Next
'================================================
Out = Temp

Exit_StripAlpha:
Exit Function

Err_StripAlpha:
MsgBox Err.Description
Resume Exit_StripAlpha

End Function

Thanks again & have a good weekend !

SD
Cheryl Fischer said:
There are really three fields which have been concatenated into one here:
RevisionPrefix, BasePartNum, RevisionSuffix; and, has been said many times
in these newsgroups, it is ALWAYS easier to concatenate multiple fields
together to display in a form or report than it is to try to split
concatenated data.

Anyway, you will need a function to test for the presence of a number in
each character of the PartNumber, when the function finds the *first*
character between 0 and 9, it presumes that that character is the first
digit of the Base Part Number. The function then returns a 6-character
string beginning with that character.

You can use the function in a query by entering the following in the first
empty column in your query:

BasePart: GetBasePart([PartNumber])

' Copy the following function to a module
Public Function GetBasePart(strPartNum As String) As String

Dim i As Integer

If Left(strPartNum, 1) >= 0 And Left(strPartNum, 1) <= 9 Then
GetBasePart = Left(strPartNum, 6)
Exit Function
End If

For i = 2 To Len(Trim(strPartNum))
If Mid(strPartNum, i, 1) >= 0 And Mid(strPartNum, i, 1) <= 9 Then
GetBasePart = Mid(strPartNum, i, 6)
Exit Function
End If
Next i

End Function

--

Cheryl Fischer, MVP Microsoft Access



Scott Duncan said:
Cheryl ,

Thank you for the reply...

Some Examples are:
01721200A
A01721200A
B01721200A
A017212R0A
B017212R0A

[Revision] + [Base Number] + [Finish]
A 017212 00A

Now, "most" Part Numbers are 9 Characters A/N
When there is a change with the part, a Revision will be prefixed.
There are also some part numbers that will have multiple [Alpha] characters
in the prefix or suffix.

All Base Part Numbers [for now] are Six (6) Digits, no Alpha.

Thank you again,

SD

Cheryl Fischer said:
If you will give us some representative examples of your Part Numbers,
perhaps we can come up with a solution.

--

Cheryl Fischer, MVP Microsoft Access



We have Part Numbers that are Alpha Numeric...

They are based on a Numeric Base Part Number. Alpha Characters, both
Prefixes and Suffixes
indicate Revision & Special Finish.

In my Query, I want to strip out all Alpha Characters from the Part Number
to reveal the Base Part Number.

Base: Val([Part Number]) does not seem to work,
nor does CLng([Part Number]).

Beating my head into my desk...

Any help would be appreciated.

SD
Click to expand...
Click to expand...
Click to expand...
 
Back
Top