Alphabetizing street names with numbers in the way

[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

 

[Cheryl Fischer]

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.

 

[Susan Kennedy]

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

 

[Cheryl Fischer]

You're welcome!

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

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

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

.

 

[George Reamy]

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
.

.

 

[George Reamy]

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

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

.

 

[Cheryl Fischer]

Thank you, George. Good luck with your project.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

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

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

.