Need a query for this alphanumeric mess

[lgvelez]

I have a database that has an important field that is an alphanumeric field that is mixed as such:<br> AB123456<br> ABC123456<br> AB123456Z<br> A123456BCD<br><br>In other words, no pattern. Is there any way that I can capture the numbers only, and remove the alpha? Thanks.&nbsp;&nbsp;Laura<br> <p>Laura Velez<br><a href=mailto:lauravelez@home.com>lauravelez@home.com</a><br><a href= > </a><br>

 

[PaulF]

Public Function ParseNumericsFromAlphas(strToBeParsed As String)<br>' This function will parse a string into Alpha characters<br>' and numeric characters<br>Dim lngLength As Long, i As Long, ParsedString As String<br>i = 1<br>lngLength = Len(strToBeParsed)<br>For i = 1 To lngLength<br>Select Case Asc(Mid(strToBeParsed, i, 1))<br>Case 48 To 57&nbsp;&nbsp;'Numerics<br>&nbsp;&nbsp;strNumeric = strNumeric & Mid(strToBeParsed, i, 1)<br>Case 65 To 90&nbsp;&nbsp;'UpperCase Alphas<br>&nbsp;&nbsp;strAlpha = strAlpha & Mid(strToBeParsed, i, 1)<br>Case 97 To 122 'LowerCase Alphas<br>&nbsp;&nbsp;strAlpha = strAlpha & Mid(strToBeParsed, i, 1)<br>Case Else<br>&nbsp;&nbsp;'do nothing<br>End Select<br>Next i<br><br>End Function

 

[caibo]

An alternative could be to use the IsDigit Function as follows.<br><br>Public Function GetDigits(strVal As String)<br><br>Dim i As Long<br><br>GetDigits = &quot;&quot;<br><br>For 1 = 1 to Len(strVal)<br>&nbsp;&nbsp;&nbsp;If IsDigit(Mid$(strVal,i,1) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GetDigits = GetDigits & Mid$(strVal,i,1) <br>&nbsp;&nbsp;&nbsp;EndIf<br>Next i<br><br>End Function

 

[PaulF]

I could not get IsDigit or for that matter,&nbsp;&nbsp;IsAlpha to work in my version of Access.<br><br>I don't have VB installed.&nbsp;&nbsp;Is this required in order to use those functions?<br><br>PaulF

 

[caibo]

I have Visual Studio installed. If you don't have VB or Visual studio, you could create your own string utility functions as follows;<br><br>Public Function IsDigit(strVar As String) As Boolean<br><br>IsDigit = False<br><br>' Check for Digit<br>If Asc(strVar) &gt; 47 And Asc(strVar) &lt; 58 Then<br>&nbsp;&nbsp;&nbsp;IsDigit = True<br>End If<br><br>End Function<br><br><br>You can use the same logic to create an IsAlpha function.<br>You may want to alter the function name slightly so you won't conflict with the library function should you acquire VB at a later date.<br>

 

[MichaelRed]

Do you mean that you don't have MS Access VBA installed?&nbsp;&nbsp;If so, then none of the above will work.&nbsp;&nbsp;Some versions of MS Access (e.g. Access 95) do not install VBA by default, so you need to check (or know) if it is installed on your system (and - if this is a multi-user setup, if it (VBA) is installed on every users machine which will use the db).<br><br>If VBA is not on every machine, the previous posts WILL NOT WORK!<br>

 

[PaulF]

No, I have VBA installed, but I don't have VB5 or VB6 or another application whose library contains the IsDigital or IsNumeric functions. That's why it won't work on my machine.&nbsp;&nbsp;Caibo apparently has the ability to use these functions due to some other library available on his/her machine.&nbsp;&nbsp;So in short what I was attempting to point out was that IsDigital is not a function supplied by Access and VBA and may not be available for use by everybody.<br><br>PaulF<br>