Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Zero (0) would be fine

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •