Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Delete Query

Discussion in 'Information Technology' started by GOL, Jul 28, 2006.

Tags:
  1. GOL

    GOL
    Expand Collapse
    Guest

    I need to delete everything before the first numeral in a field.
    For example, I have: Old Hickory, TN, 99385
    I want this to only read the zip: 99385

    Help please......
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    If the zip is the last item in the FullAddress field, you could use:
    ParseWord([FullAddress], -1, " ", True, True)
    where the ParseWord() function is copied from here:
    http://allenbrowne.com/func-10.html

    Or, you could pull all the numbers out of the field with:
    DropNonDigit([FullAddress])
    where the DropNonDigit() function is below.

    Function DropNonDigit(varIn As Variant) As Variant
    Dim i As Integer
    Dim strOut As String
    Dim iChar As Integer

    If Not IsNull(varIn) Then
    For i = 1 To Len(varIn)
    iChar = Asc(Mid(varIn, i, 1))
    If iChar >= vbKey0 And iChar <= vbKey9 Then
    strOut = strOut & Chr$(iChar)
    End If
    Next
    End If

    If strOut = vbNullString Then
    DropNonDigit = Null
    Else
    DropNonDigit = strOut
    End If
    End Function

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "GOL" <GOL@discussions.microsoft.com> wrote in message
    news:2EAC2046-9337-4C60-A9F1-0D03B7E1B27D@microsoft.com...
    >I need to delete everything before the first numeral in a field.
    > For example, I have: Old Hickory, TN, 99385
    > I want this to only read the zip: 99385
    >
    > Help please......
     
  4. GOL

    GOL
    Expand Collapse
    Guest

    Thanks, the DropNonDigit function works well, however the ParseWord seems to
    have a few errors when ran. I could not pinpoint code problem.

    "GOL" wrote:

    > I need to delete everything before the first numeral in a field.
    > For example, I have: Old Hickory, TN, 99385
    > I want this to only read the zip: 99385
    >
    > Help please......
     
  5. efgh

    efgh
    Expand Collapse
    Guest

    "GOL" <GOL@discussions.microsoft.com> wrote in message
    news:2EAC2046-9337-4C60-A9F1-0D03B7E1B27D@microsoft.com...
    >I need to delete everything before the first numeral in a field.
    > For example, I have: Old Hickory, TN, 99385
    > I want this to only read the zip: 99385
    >
    > Help please......


    Have you tried Field = Right(Trim(Field), 5) ?
    This assumes that the zip code is always the last 5 characters in the field.
     

Share This Page