Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Does it start with a number? (Access 97-->)

    I have been provided with a csv download of addresses. The data is exteremely "dirty" and needs a good spring clean as it will eventually <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16> be used as a lookup for users. The problem is that the data is presented as follows:

    123-125 Acacia Avenue
    145-189 Beta House
    Cardinal Mews 34-43
    Delta Place 45- 89
    1-9 Epsilon Place


    I would like to present it as

    Acacia Avenue 123-125
    Beta House 145-189
    Cardinal Mews 34-43
    Delta Place 45- 89
    Epsilon Place 1-9
    Epsilon Place 10-19

    Any ideas how I can use a query/function that could check it the first character in the string is a numeric so that I can do a conversion on just that string?
    Jerry

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

    Re: Does it start with a number? (Access 97-->)

    If none of the numbers start with 0, you can use the Val function. If Val([NameOfField]) is > 0, the field value starts with a number.
    Another way is to check if Asc([NameOfField]) is between 48 and 57 (the ASCII/ANSI codes for the digits 0 and 9, respectively)

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Does it start with a number? (Access 97-->)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Jerry

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Does it start with a number? (Access 97-->)

    Hans

    Just posting the solution for archive purposes if Loungers have a similar problem. This is connected with <post#=446618>post 446618</post#>

    Function ChangeIt(strBlock As String)
    Dim TheGap As Integer
    Dim TheNo As String
    Dim TheAddress As String
    Dim theFull As String

    If Val(strBlock) > 0 Then

    TheGap = InStr(strBlock, " ")

    TheNo = Trim(Left(strBlock, TheGap - 1))

    TheAddress = Trim(Mid(strBlock, TheGap + 1))

    theFull = TheAddress & " " & TheNo

    ChangeIt = theFull

    Else

    ChangeIt = strBlock

    End If
    End Function

    The function went in a query and ran like a rocket.
    Jerry

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

    Re: Does it start with a number? (Access 97-->)

    Thanks for sharing the solution.

Posting Permissions

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