Stripping a field in a query? 1

[gi11ies]

Hi, I wish to write a query that orders values in an address book database by town, and then street ... in a simple database like the following:

id : autonumber
name : text
street: text
town: text
area: text
postcode: text
email: text
telephone: text

I know a simple query like the following will do:

SELECT * FROM addressbook ORDER BY town, street;

BUT

what if the street names are like:

15 Any Street, and 26 This Street etc etc etc

Is there anyway of stripping the street name fields value so it orders it by street name? Such as in the query, have it look up the first space in the street field, and order it by the next character, which should be the first character of the street name?

The existing basic database I already have is pretty full, and I dont want to have to add another field for Street Number into it, and have to edit every single current street, so I have street number and street name ie [26] [This Street].

Any suggestions on how to get around this? or if its possible?

Gillies

 

[drewby1]

Hi Gillies

You need to find the first space from the left of the address then select from the right hand side of street up to this position

Give this a try. I tested on a dummy table & query and it seemed to work fine

SELECT addressbook.*, FROM addressbook
ORDER BY town, Right(street,Len (street)-InStr(street, " &quot-1);

If you want to view the query and the street without it's house number/name, you can create a temporary field in your query as follows...

SELECT addressbook.*, (Right(street,Len (street)-InStr(street, " &quot)) AS streetWithNoNumber FROM addressbook
ORDER BY town, Right(street,Len (street)-InStr(street, " &quot);

NOTE:
This assumes that the street consists of house number/name followed by a space followed by the street name

eg.
123 high street
2 mill lane south
stonegarth country lane

It won't work if you have something like...

12 A High Street or
Happy House Smiley Street

Good luck