# Follow Up ? -> 2 Tables Comparison

http://www.experts-exchange.com/Databases/MS_Access/Q_21059341.html
http://www.experts-exchange.com/Databases/MS_Access/Q_21054826.html
http://www.experts-exchange.com/Databases/MS_Access/Q_21054509.html
------------------------------------------------------------------------------------------

Last time i promiss, I want to change the new code (See Below) to select 3 characters (instead of 2) to compare after the numbers... again ignoring spaces and caps...
Please see the previous questions to understand what it is I am trying to accomplish here...
-BassKozz

<<<START CODE>>>
Function Val1Char(strInput As String) As String
Dim i As Integer
Dim StrLenght As Integer
strInput = Replace(strInput, " ", "")
For i = 1 To Len(strInput)
If Not IsNumeric(Mid(strInput, i, 1)) Then
StrLenght = i + 1
Exit For    'exit after finding the first number from left
End If
Next i

Val1Char = IIf(StrLenght = 0, "na", Left(strInput, StrLenght))

End Function
<<<END CODE>>>
###### Who is Participating?

x

Database and Application DeveloperCommented:
Function Val1Char(strInput As String) As String
Dim i As Integer
Dim StrLenght As Integer
strInput = Replace(strInput, " ", "")
For i = 1 To Len(strInput)
If Not IsNumeric(Mid(strInput, i, 1)) Then
StrLenght = i
Exit For    'exit after finding the first number from left
End If
Next i

Val1Char = IIf(StrLenght = 0, "na", Left(strInput, (StrLenght , 2)))

End Function

Above function will return:

123east = 123 easxtern (123eas=123eas)
123 east = 123easxtern (123eas=123eas)
222 Smith = 222 Smitty (222smi=222smi)

Examples of what NOT to collect or match....

111 east  ---------> 111eas
111east   ---------> 111eas
123 east  ---------> 123eas
123east   ---------> 123eas
111 eat   ---------> 111eat
111eat    ---------> 111eat

Comparing them is a different story.  It ought to be handled with a the query.

Mike
0

Database and Application DeveloperCommented:
For three characters after first number use:

Function Val1Char(strInput As String) As String
Dim i As Integer
Dim StrLenght As Integer
strInput = Replace(strInput, " ", "")
For i = 1 To Len(strInput)
If IsNumeric(Mid(strInput, i, 1)) Then
StrLenght = i + 1
Exit For    'exit after finding the first number from left
End If
Next i

Val1Char = IIf(StrLenght = 0, "na", Mid(strInput, StrLenght , 3))

End Function
0

Database and Application DeveloperCommented:
Last example will pick:

3RTUX     first number is 3, therefor will return RTU

356RTUX     first number is 3, therefor will return 56R

----------------------------
To select RTU  form both examples, I guess what you are asking for, try:

Function Val1Char(strInput As String) As String
Dim i As Integer
Dim StrLenght As Integer
strInput = Replace(strInput, " ", "")
For i = 1 To Len(strInput)
If Not IsNumeric(Mid(strInput, i, 1)) Then
StrLenght = i
Exit For    'exit after finding the first number from left
End If
Next i

Val1Char = IIf(StrLenght = 0, "na", Mid(strInput, StrLenght , 3))

End Function
------------
Provided that your string never starts with text characters.

Mike
0

Database and Application DeveloperCommented:

RTUX   you will get RTU using second post.
----------
If you are looking to extrach RUT from say:

ABC12RUTX

Meaning, there is going to be some numbers always, a third code is required to do that.  I will wait for your response.

Mike
0

Author Commented:
Example of what I would like it to do...

123east = 123 easxtern (123eas=123eas)
123 east = 123easxtern (123eas=123eas)
222 Smith = 222 Smitty (222smi=222smi)

Examples of what NOT to collect or match....

111 east =/ 123 east  (because numbers don't match)
111 east =/ 111 eat   (because "eas" doesn't equal "eat")

0

Author Commented:
Mike,

Syntax Error :
Val1Char = IIf(StrLenght = 0, "na", Left(strInput, (StrLenght , 2)))

0

Author Commented:
It might be easier to see what the code does in my Database... so I have put up links to the database...

<<<OR>>>

*.mdb file = 2056kb
*.rar file   = 276kb

0

Database and Application DeveloperCommented:
change , with + in

Val1Char = IIf(StrLenght = 0, "na", Left(strInput, (StrLenght + 2)))
0

Author Commented:
It works,
Thanks Mike

-BassKozz
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.