Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    10

    Unanswered: inserting between numbers and letters in one column

    in a street address column, i mistakenly imported the street numbers right next to the street names and need to know how to add a space between the two.

    there aren't a fixed number of street numbers though..(some have 2#'s, 3#'s,etc....)

  2. #2
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133

    Re: inserting between numbers and letters in one column

    You'll have to create a function to perform this job. This could do the trick :

    Function FindNum(strText As String) As String

    Dim i As Integer

    For i = Len(strText) To 1 Step -1

    If Not IsNumeric(Mid(strText, i)) Then
    FindNum = Left(strText, i) & " " & Mid(strText, i + 1)
    Exit For
    End If
    Next

    End Function

  3. #3
    Join Date
    Dec 2003
    Posts
    10
    thanks for the reply...

    not sure how you create a function though.

    i can somewhat follow the code you wrote there but i'm not certain how it's implemented.

    do i set up an update query and then paste it into the sql code?

    i'm obviously kinda new to this...really appreciate the help though...

  4. #4
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    Fast Access lesson : How to create a function.....

    -Open a new module
    -copy the full code into it
    -close and save the module

    You have now created the function FindNum that you can use through your database just as you should use the function Sum() or Count() or whatever other function.

    You make an update query and use the function to update the Street-field.
    If that field is called "Street", you must update the field [Street] to FindNum([Street])


    Quite simple isn't it?

  5. #5
    Join Date
    Dec 2003
    Posts
    10
    yup. that is pretty simple. thank you for the explanation.

    unfortunetly, the code isn't working.

    any idea how i could modify...should it be working?

    could the problem be that there are apt #'s that follow the text in some records? i should have mentioned that.

    any ideas?

  6. #6
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    Originally posted by gutterz
    yup. that is pretty simple. thank you for the explanation.

    unfortunetly, the code isn't working.

    any idea how i could modify...should it be working?

    could the problem be that there are apt #'s that follow the text in some records? i should have mentioned that.

    any ideas?
    Yes, you're right. The function will only work if the characters on the end of your text are numbers. The function should work then as well but it will not work correctly.

    It will convert for example "WallStreet135" into "WallStreet 135" but also "WallStreet135app25" into "WallStreet135app 25"

    The function I gave you should work for most of the records. I'm afraid you'll have to do the others manually....


    If you need changing the function, just open the module again and change anything you want.
    After that, compile the code (command <Compile> in menu <Debug> in the VB window) and save your module again.

Posting Permissions

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