Aaron said:
Good morning. I have a list of model numbers in which some start with
letters and some start with numbers. For example, I have:
4803
LV4803
LOV4803
O4803
I do not know programming, but I'm hoping there is a formula I can use in
a
query that will return the first 3 numbers of this list, such as:
480
480
480
480
Any help would be much appreciated.
You need a VBA function to extract just the numeric digits you want. I
happen to have such a function lying around:
'------ start of code ------
Function fncDigitsOnly(varOldNumber As Variant) As Variant
' Removes any non-numeric characters in a string, returning
' only the numeric digits. Returns Null if argument is Null.
Dim I As Integer
Dim intLength As Integer
Dim intDigits As Integer
Dim strThisCharacter As String
Dim strOldNumber As String
Dim strNewNumber As String
If IsNull(varOldNumber) Then
fncDigitsOnly = Null
Exit Function
End If
strOldNumber = varOldNumber & vbNullString
intLength = Len(strOldNumber)
strNewNumber = strOldNumber
For I = 1 To intLength
strThisCharacter = Mid$(strOldNumber, I, 1)
Select Case Asc(strThisCharacter)
Case 48 To 57
intDigits = intDigits + 1
Mid$(strNewNumber, intDigits, 1) = strThisCharacter
End Select
Next I
fncDigitsOnly = Left$(strNewNumber, intDigits)
End Function
'------ end of code ------
Copy that code and paste it into a standard module. If you're creating a
new module for the purpose, make sure you don't give the module the same
name as the function.
The function returns just the numeric digits out of a string. Since you
want just the first 3 numeric digits, your query will need to trim the
result using the Left() function, using an expression like:
Left(fncDigitsOnly([ModelNumber]), 3)
--
Dirk Goldgar, MS Access MVP
Access tips:
www.datagnostics.com/tips.html
(please reply to the newsgroup)
