# Thread: How to Convert Text Field To Numerics Only? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

1. ## How to Convert Text Field To Numerics Only? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

I have the following strInitLicNo (initials and license no) text field in a table. I want to update another text field strLicNo in the same table with just the numeric portion from strInitLicNo

How do I do this?

Thanks, John

CS 140
CNS 519
DG 872
CNS 146
DG
CS 320
DG 798
DRG 633
CNS 266
DG
CNS 394
CNS907
CNS 793
DG 7318
CNS 143

2. ## Re: How to Convert Text Field To Numerics Only? (a2k (9.0.6926) SP-3 Jet 4

What should a value such as "DG" become? Zero (0) or null (blank)?

3. ## Re: How to Convert Text Field To Numerics Only? (a2k (9.0.6926) SP-3 Jet 4

Zero (0) would be fine

4. ## Re: How to Convert Text Field To Numerics Only? (a2k (9.0.6926) SP-3 Jet 4

Put the following function in a standard module:

Function GetNumber(varValue As Variant) As Long
Dim i As Integer
Dim a As Integer
GetNumber = 0
If IsNull(varValue) Then
Exit Function
End If
For i = 1 To Len(varValue)
a = Asc(Mid(varValue, i, 1))
If a > 47 And a < 58 Then
GetNumber = Val(Mid(varValue, i))
Exit For
End If
Next i
End Function

Use the expression GetNumber([strInitLicNo]) in the Update to line of the query.

5. ## Re: How to Convert Text Field To Numerics Only? (a2k (9.0.6926) SP-3 Jet 4

Great Code

CS 056 is returning 56 I need 056, I changed

Function GetNumber(varValue As Variant) As Long
to
Function GetNumber(varValue As Variant) As String

But that didn

6. ## Re: How to Convert Text Field To Numerics Only? (a2k (9.0.6926) SP-3 Jet 4

Keep the As String declaration, and change the line

GetNumber = Val(Mid(varValue, i))

to

GetNumber = Mid(varValue, i)

7. ## Re: How to Convert Text Field To Numerics Only? (a2k (9.0.6926) SP-3 Jet 4

Hi Hans

GetNumber works fine, thanks for your help.

Trying to adapt the following to separate just the text portion GetText, however CNS 266 input is returning CNS 266 output

What am I missing?

Thanks, John

<pre>Function GetText(varValue As Variant) As String
Dim i As Integer
Dim a As Integer
GetText = "" 'return spaces if no text
If IsNull(varValue) Then
Exit Function
End If
For i = 1 To Len(varValue)
'Returns an Integer representing the character code corresponding to the
'first letter in a string.
a = Asc(Mid(varValue, i, 1))
If a > 64 And a < 91 Then ' between A and Z
GetText = Mid(varValue, i)
Exit For
End If
Next i
End Function
</pre>

8. ## Re: How to Convert Text Field To Numerics Only? (a2k (9.0.6926) SP-3 Jet 4

The code looks for the first upper case letter in the value, and returns everything from that position to the right. You should look for the last letter, and return everything up to and including that position.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•