Access 2000 - Removing data from a field

  ihbuk1 10:30 09 Oct 07
Locked

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?

Thanks

  silverous 11:22 09 Oct 07

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 07

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 07

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.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…