Trimming Again!!

[zashmore]

I've looked but cannot find my answer.

I am changing the way my works record repairs. At present they are using an excel worksheet but I said that Access would, of course, be much more productive.

I have transferred 600+ addresses over but as it's previously in excel everything is in one field, (House no, Street name etc..)

I need to get rid of the house no's. I know how to trim from left but not sure how to do this as some fields have no's and some don't eg -

7 Halford House
Clapham Court
27 Nightingale House etc..

How do I just get rid of the no's, preferrably in a query?

Thanks

 

[neileg]

Use IsNumeric on the first character. If it isn't numeric, do nothing. If it is, use InStr to find the position of the first space and then Right to pick everthing to the right of the space.

Of course there's bound to be some data that makes this fall over, a leading space for instance.

 

[zashmore]

Sorry to be a pain the A** and getting you to do the work but how would I input that?

Cheers

 

[MSAccessRookie]

Use IsNumeric on the first character. If it isn't numeric, do nothing. If it is, use InStr to find the position of the first space and then Right to pick everthing to the right of the space.

Of course there's bound to be some data that makes this fall over, a leading space for instance.

I was thinking the same thing, but rejected it because another (more likely) example of what could make it fail is a street name with a number that is not spelled out into a word. (EX: 5th Avenue returns "th Avenue"). If it is really only about 600 items, I think it might not be too many to do by hand.

 

[dkinley]

Eh ... it's only 600 +/- 50. You could do em by hand instead of waiting a day or two trying to figure it out. Use the Ctrl+C and Ctrl+V to make it go faster.

Suggestion, move the numbers side to another field so you can concantenate them for reports, etc.

-dK

 

[neileg]

Fow what it's worth, this would be the basis of a calculated field in a query:

Iif(IsNumeric(Left("MyField",1), Right("MyField",Instr("MyField"," ")),[MyField])

Note: Aircode, untested.

Actually, 5th Avenue would come out as just Avenue with this calculation.

I agree that with 600 records I would handball it too.

 

[zashmore]

Thanks all for your replys.

By the time it took me to log back in to see the replys I have done them by hand.

Good to know for future reference though.

Thanks again.

 

[dkinley]

Hehe =]

Now that the crisis is passed, it allows you time to develop the code for the next batch.

-dK