Access 2000 - Removing data from a field

  ihbuk1 10:30 09 Oct 2007

I want to remove the house numbers from a street name e.g. 111 Hotspur Road to Hotspur Road. Some of the information will also be like 111a Hotspur Road and other will just have the street name. - Any ideas?


  silverous 11:22 09 Oct 2007

Is this an update you want to apply across an entire table in the database or just case-by-case on a form or something?

Will there ALWAYS be a number at the start? i.e. what if that fields has

The Gables, Hotspur Road

in it?

Addresses are quite difficult to clean unless you know they are consistent. You can get software/databases to clean against.

  ihbuk1 11:37 09 Oct 2007

The house name is in a separate field, but the street field can mostly contains the house number, but in some instances just the street name. I want to remove the house number, just keeping the street name.

  silverous 13:09 09 Oct 2007

I don't think you've answered my first question though:

Are you looking to update the entire set of data i.e. with an update query to clean it all up?

This impacts on how it is sorted, as the code will be different to doing it on each record on a form for example.

Would the logic:

"If the first character of the street field is a number then remove everything up to the next space".

Work in all cases you can think of? If so, and if you want to update all instances in your table, something like this:

click here

i.e. an update query which, if it finds a number at the first character replaces the whole street with everything after the first space.

how's that?

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iMac Pro review

Why this awful City of Los Angeles job ad for a graphic designer is actually brilliant

iMac Pro review

Les meilleures prises CPL (2018)