Solved

Find numeric part of a text field

Posted on 2007-04-01
3
189 Views
Last Modified: 2010-04-30
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)
   
0
Comment
Question by:sgs1970
3 Comments
 
LVL 12

Expert Comment

by:StuFox100
ID: 18834903
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
0
 
LVL 14

Accepted Solution

by:
Matti earned 125 total points
ID: 18835154
Hi!

This is a quick and simple function that I made for you.

Private Sub Command1_Click()
MsgBox finnumber("12 - A")
End Sub

Private Function finnumber(s As String) As String
Dim mychr As String * 1, myasc As Integer
For i = 1 To Len(s)
mychr = Mid$(s, i, 1)
myasc = Asc(mychr)
Select Case myasc
Case 48 To 57
finnumber = finnumber + mychr
Case Else
End Select
Next i
End Function


Matti
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 18835296
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now