Results 1 to 2 of 2
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    A Magic Forest in Deepest, Darkest Kent
    Thanked 1 Time in 1 Post

    Delete first part of field (97 )

    I have been given a project which involves housing properties. My problem involves getting rid of the house numbers at the first part of the field as the data has been set up with the house/flat number and the road in the same field. The second problem is that it comes on different formats as listed below:
    1 Acacia Avenue
    1-11 Acacia Avenue
    11 Acacia Avenue
    111 Acacia Avenue

    Is there some code can loop through the data and removes these spurious numbers and hyphens which stops at the First Letter so that I can sort by road name and not the house number.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Delete first part of field (97 )

    (1) there are no spaces within the house number, and
    (2) there is only one space between the house number, and
    (3) the house number is never absent,

    it's easy (but make sure to test it on a copy of your data first!)

    (1) create a query based on the address table
    (2) add the field
    (3) make the query into an update query
    (4) enter the following expression in the Change To line:

    Mid([fieldname],InStr([fieldname]," ")+1)

    (5) Run the query. (Again: test it first!)

    where you must replace fieldname by the actual name of your field.

    If the house numbers may be lacking, may contain spaces and letters (1-A, for instance), it gets more complicated. In that case, you probably need a VBA function. If so, post back and indicate in more detail what types of house number are possible.

Posting Permissions

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