Alphabetizing street names with numbers in the way

  • Thread starter Thread starter George Reamy
  • Start date Start date
G

George Reamy

I figure I can't do this, but I thought I'd ask:

I have a political database with street address info
appearing in one field like this: "705 Palm Ave." I need
to add the precinct number for hundreds of records, and
it would go a lot more quickly if I could alphabetize by
street name, since my precinct reference is organized
that way. Is there a way to get Access to ignore the
numbers so I can order the field/records by street?

Thanks.

--George Reamy
 
You could try something like the following in a Query:

StreetOnly: Mid([Address], InStr([Address], " ") + 1)

Add this calculated field to a query and select Sort Ascending.

The expression works for addresses which begin with a number and have the
Street name immediately after the number, as in the example you provided:
705 Palm Ave.
 
George,

Can you say that the name of the street almost always
starts after the first space? If so, you can add to a
query a formula like:

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

which will grab the part of the address field after the
space. Then you can sort on the formula.

Susan
 
You're welcome!

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Jim/Chris said:
Thanks Cheryl. That will be very usefull to me

Jim
-----Original Message-----
You could try something like the following in a Query:

StreetOnly: Mid([Address], InStr([Address], " ") + 1)

Add this calculated field to a query and select Sort Ascending.

The expression works for addresses which begin with a number and have the
Street name immediately after the number, as in the example you provided:
705 Palm Ave.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

George Reamy said:
I figure I can't do this, but I thought I'd ask:

I have a political database with street address info
appearing in one field like this: "705 Palm Ave." I need
to add the precinct number for hundreds of records, and
it would go a lot more quickly if I could alphabetize by
street name, since my precinct reference is organized
that way. Is there a way to get Access to ignore the
numbers so I can order the field/records by street?

Thanks.

--George Reamy
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks! I'll give it a shot. --George
-----Original Message-----
George,

Can you say that the name of the street almost always
starts after the first space? If so, you can add to a
query a formula like:

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

which will grab the part of the address field after the
space. Then you can sort on the formula.

Susan
-----Original Message-----
I figure I can't do this, but I thought I'd ask:

I have a political database with street address info
appearing in one field like this: "705 Palm Ave." I need
to add the precinct number for hundreds of records, and
it would go a lot more quickly if I could alphabetize by
street name, since my precinct reference is organized
that way. Is there a way to get Access to ignore the
numbers so I can order the field/records by street?

Thanks.

--George Reamy
.
Click to expand...
.
Click to expand...
 
You're a life-saver, Cheryl! --George
-----Original Message-----
You could try something like the following in a Query:

StreetOnly: Mid([Address], InStr([Address], " ") + 1)

Add this calculated field to a query and select Sort Ascending.

The expression works for addresses which begin with a number and have the
Street name immediately after the number, as in the example you provided:
705 Palm Ave.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

George Reamy said:
I figure I can't do this, but I thought I'd ask:

I have a political database with street address info
appearing in one field like this: "705 Palm Ave." I need
to add the precinct number for hundreds of records, and
it would go a lot more quickly if I could alphabetize by
street name, since my precinct reference is organized
that way. Is there a way to get Access to ignore the
numbers so I can order the field/records by street?

Thanks.

--George Reamy
Click to expand...


.
Click to expand...
 
Thank you, George. Good luck with your project.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

George Reamy said:
You're a life-saver, Cheryl! --George
-----Original Message-----
You could try something like the following in a Query:

StreetOnly: Mid([Address], InStr([Address], " ") + 1)

Add this calculated field to a query and select Sort Ascending.

The expression works for addresses which begin with a number and have the
Street name immediately after the number, as in the example you provided:
705 Palm Ave.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

George Reamy said:
I figure I can't do this, but I thought I'd ask:

I have a political database with street address info
appearing in one field like this: "705 Palm Ave." I need
to add the precinct number for hundreds of records, and
it would go a lot more quickly if I could alphabetize by
street name, since my precinct reference is organized
that way. Is there a way to get Access to ignore the
numbers so I can order the field/records by street?

Thanks.

--George Reamy
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top