Access: 2003 How to Remove Alpha Characters In a Query

[bknabl]

I have a list of many part numbers of varying length and formats that I am trying to obtain the last two numerics within that part number. the part number could be like cr2344-32-05 or it could be 3239ew06wx. I need to return only the 05 or 06. I have tried to test this in a text box using some code I found on microsoft.

Function RemoveAlphas (ByVal AlphaNum as Variant)

   Dim Clean As String
   Dim Pos, A_Char$

   Pos = 1
   If IsNull(AlphaNum) Then Exit Function

   For Pos = 1 To Len(AlphaNum)
      A_Char$ = Mid(AlphaNum, Pos, 1)
      If A_Char$ >= "0" And A_Char$ <= "9" Then
         Clean$ = Clean$ + A_Char$
      End If
   Next Pos

   RemoveAlphas = Clean$

End Function

Then I used the code:

Private Sub txtTest_AfterUpdate()

Me![txtTest] = RemoveAlphas(Me![txtTest])

End Sub

When the AfterUpdate is activated, it gives me an error saying Compile Error: Expected variable or proceedure, not module.

Im not really sure how to fix this problem. Thanks in advance!

[patjones]

The code runs perfectly fine for me as is. Are you sure that you put the code inside the module for the form in question?

Pat

[ADezii]

I have a list of many part numbers of varying length and formats that I am trying to obtain the last two numerics within that part number. the part number could be like cr2344-32-05 or it could be 3239ew06wx. I need to return only the 05 or 06. I have tried to test this in a text box using some code I found on microsoft.

Function RemoveAlphas (ByVal AlphaNum as Variant)

   Dim Clean As String
   Dim Pos, A_Char$

   Pos = 1
   If IsNull(AlphaNum) Then Exit Function

   For Pos = 1 To Len(AlphaNum)
      A_Char$ = Mid(AlphaNum, Pos, 1)
      If A_Char$ >= "0" And A_Char$ <= "9" Then
         Clean$ = Clean$ + A_Char$
      End If
   Next Pos

   RemoveAlphas = Clean$

End Function

Then I used the code:

Private Sub txtTest_AfterUpdate()

Me![txtTest] = RemoveAlphas(Me![txtTest])

End Sub

When the AfterUpdate is activated, it gives me an error saying Compile Error: Expected variable or proceedure, not module.

Im not really sure how to fix this problem. Thanks in advance!

The code just removes the Alpha Characters from the String but aren't you looking for the Last Two Numerics withing the String? I made slight modifications within the existing code to produce the desired results.

Public Function RemoveAlphas2(ByVal AlphaNum As Variant)
Dim Clean As String
Dim Pos As Integer
Dim A_Char As String

Pos = 1

If IsNull(AlphaNum) Then Exit Function
  
For Pos = 1 To Len(AlphaNum)
  A_Char = Mid(AlphaNum, Pos, 1)
    If A_Char >= "0" And A_Char <= "9" Then
      Clean = Clean + A_Char
    End If
Next Pos

'Return the last 2 Numeric Values
If Len(Clean) > 1 Then
  RemoveAlphas = Right(Clean, 2)
Else
  RemoveAlphas = Null
End If
End Function

[patjones]

The code just removes the Alpha Characters from the String but aren't you looking for the Last Two Numerics withing the String? I made slight modifications within the existing code to produce the desired results.

Public Function RemoveAlphas2(ByVal AlphaNum As Variant)
Dim Clean As String
Dim Pos As Integer
Dim A_Char As String

Pos = 1

If IsNull(AlphaNum) Then Exit Function
  
For Pos = 1 To Len(AlphaNum)
  A_Char = Mid(AlphaNum, Pos, 1)
    If A_Char >= "0" And A_Char <= "9" Then
      Clean = Clean + A_Char
    End If
Next Pos

'Return the last 2 Numeric Values
If Len(Clean) > 1 Then
  RemoveAlphas = Right(Clean, 2)
Else
  RemoveAlphas = Null
End If
End Function

He is saying that the code won't compile; for me it compiled and executed fine just the way it is written. I figured we'd add the Right(Clean, 2) bit after getting through the first difficulty.

Pat

[bknabl]

Got it to work. Thank you!

[ADezii]

He is saying that the code won't compile; for me it compiled and executed fine just the way it is written. I figured we'd add the Right(Clean, 2) bit after getting through the first difficulty.

Pat

Gotcha, thanks zepphead80.

[ADezii]

Got it to work. Thank you!

How about letting us know what the problem was?

[NeoPa]

I expect that is due to an error in some versions of Access where it gets confused if you use the same name for a procedure as for the module itself.

Generally a good idea to avoid that anyway to be fair.