Link to home
Start Free TrialLog in
Avatar of sgs1970
sgs1970

asked on

Find numeric part of a text field

Hi,
   I have the house numbers coming from the MSAccess text field.
  (The database can be oracle, anyway)
  In my vb6 code I have to assign the next house no.
  A sample may be
  100
  200-A
  2-F
 40
 
 I want to see the numeric part of each of these and find the highest and add 1
 The requirment is to find the next house number  to propose .(In this case it should be 201)
   
Avatar of StuFox100
StuFox100
Flag of Australia image

hI sqs1970
This link has a few options on how to locate the numbers only:
http://www.thescripts.com/forum/thread388326.html
Using the above you should be able to convert the variables to an array of numbers and find the max.
Cheers
Stu
ASKER CERTIFIED SOLUTION
Avatar of Matti
Matti
Flag of Finland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
i'd make a few amendments....
1/to handle addresses like 24A 22nd Street          you only want the 24.
2/ return a number.  an int would be ok usually but there are a couple of 33000 numbers around.

Private Function AddressNumber(sAddress As String) As long
      Dim mychr As String * 1, sTemp as string, i as integer
      For i = 1 To Len((sAddress)
            mychr = Mid$(s, i, 1)
            
            Select Case Asc(mychr)
                  Case 48 To 57
                        sTemp = sTemp & mychr
                  Case Else
                        if sTemp <> "" then
                              'have already found the number
                              'so stop looking
                              Exit For
                        end if
            End Select
      Next i
      AddressNumber = val(sTemp)
      
End Function