Need a query for address field

[Iwilfix]

I have an address field in my table with street numbers and names
( 248 elm St. , 583 Spring Rd. etc. )
Is it possible to create a query that will put all the street names
in alpabetical order?
I am a simpleton, and need it step by step. Sorry about that.

Thanks, Jeff V.

 

[Cheryl Fischer]

Presuming that all of your address fields are consistent; i.e., a number
followed by a street, this should work:

1. In your query's design view, enter the following in the first row of an
empty column:

StreetName: Mid([MyAddressField], InStr([MyAddressField], " ") + 1)

2. In the Sort: row of that column, select Ascending (or Descending)


hth,

 

[pat]

In the query section, click on New, then add the table,
add the fields in the grid. Use the Ascending value from
the "sorted" row when you are looking a the QBE grid, this
is where you are designing the query.

 

[Iwilfix]

Presuming that all of your address fields are consistent; i.e., a number
followed by a street, this should work:

1. In your query's design view, enter the following in the first row of an
empty column:

.......I told you I was dumb. With "First row" do you mean " Field"

StreetName: Mid([MyAddressField], InStr([MyAddressField], " ") + 1)

..... Where do I put this? In the criteria cell? Do I need to make
2 columns? Do I need to enter this in " Address"collumn?
Do I need to make a new collumn and call it " Street name"?

2. In the Sort: row of that column, select Ascending (or Descending)


hth,

So I now Have a collumn with "Address" in the field cell
The sort is set for " ascending"
What do i need to enter EXACTLY in the criteria cell?

 

[Cheryl Fischer]

Yes, that is the Field: row.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Presuming that all of your address fields are consistent; i.e., a number
followed by a street, this should work:

1. In your query's design view, enter the following in the first row of an
empty column:

......I told you I was dumb. With "First row" do you mean " Field"

StreetName: Mid([MyAddressField], InStr([MyAddressField], " ") + 1)

..... Where do I put this? In the criteria cell? Do I need to make
2 columns? Do I need to enter this in " Address"collumn?
Do I need to make a new collumn and call it " Street name"?

2. In the Sort: row of that column, select Ascending (or Descending)


hth,

So I now Have a collumn with "Address" in the field cell
The sort is set for " ascending"
What do i need to enter EXACTLY in the criteria cell?

 

[Iwilfix]

That much I knew. However, merly putting ascending in the
sort option, puts ALL address info in ascending order. Numbers and all
in other words ....1 Elm St. 1 Spring Rd. 10 Elm St. 10 Spring Rd. 101
Elm St. 101 Spring Rd. 11 Elm St. 11 Spring Rd.
It only put the NUMARIC value in Ascending order.
I need the STREETNAMES to be in ascending order while still keeping
their respective numbers to match the remaining table information.
Can you help me with that?

 

[Iwilfix]

I'm Sorry Cheryl. but that was not my only question.
there were several more.

StreetName: Mid([MyAddressField], InStr([MyAddressField], " ") + 1)

..... Where do I put this? In the criteria cell? Do I need to make
2 columns? Do I need to enter this in " Address"collumn?
Do I need to make a new collumn and call it " Street name"?

So I now Have a collumn with "Address" in the field window
The sort is set for " ascending"
What do i need to enter EXACTLY in the criteria window

I just can't emphisize how dumb i am, or how new i am to access.

I do however appreciate all the help this group helps.

thanks again. Jeff V.




n Thu, 5 Feb 2004 20:09:52 -0600, "Cheryl Fischer"

 

[Cheryl Fischer]

You left quite a bit of space after your first question. My apologies for
not scrolling down.

StreetName: Mid([MyAddressField], InStr([MyAddressField], " ") + 1)

..> .... Where do I put this?

You would put this in the Field: row of an empty column. It is a
"calculated field" which strips off the street number from your address,
leaving the street name, which is what you indicate you want to use to sort
your query.

In the criteria cell?
No.

Do I need to make 2 columns?

If you want to show the complete address (including the street number),
include the Address field in your query.

Do I need to enter this in " Address"collumn?

No, leave the Address column alone.

Do I need to make a new collumn and call it " Street name"?

Answered above.

So I now Have a collumn with "Address" in the field window
The sort is set for " ascending"

If you want to sort your query by the Name of the Street, use the Sort:
Ascending row in the column for the "calculated field", StreetName, and
remove the sort setting from the column containing your Address field.

What do i need to enter EXACTLY in the criteria window

Criteria are expressions which let you select the specific records which are
returned by the query. If you want all of the records in your table
returned by the query, leave the Criteria: row blank for all columns. If
you do want specific records returned by your query, please indicate which
records you want, as you did not mention this in your initial post.



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I'm Sorry Cheryl. but that was not my only question.
there were several more.

StreetName: Mid([MyAddressField], InStr([MyAddressField], " ") + 1)

..... Where do I put this? In the criteria cell? Do I need to make
2 columns? Do I need to enter this in " Address"collumn?
Do I need to make a new collumn and call it " Street name"?

So I now Have a collumn with "Address" in the field window
The sort is set for " ascending"
What do i need to enter EXACTLY in the criteria window

I just can't emphisize how dumb i am, or how new i am to access.

I do however appreciate all the help this group helps.

thanks again. Jeff V.




n Thu, 5 Feb 2004 20:09:52 -0600, "Cheryl Fischer"

Yes, that is the Field: row.

 

[Iwilfix]

Cheryl

Thank you for sticking it out with me. I thought the way I decifered
it was wrong, because I kept getting syntax errors, And as I adjusted
according to the help window, It kept getting worse.
I Asked Tim for help because he explained things so idiot proof to me.
I now followed Steve's instruction stepby step. and the same syntax
errors appeared. So followed your instructions correctly after all.
As it turned out. As I clicked down, the table name was entered in its
field. When I finaly realized and corrected this, the query worked
exactly as I had hoped it would. And will make my spring workload
so much easier to accompish.
Again thank you so much for you patients and help

Jeff V.

You left quite a bit of space after your first question. My apologies for
not scrolling down.

StreetName: Mid([MyAddressField], InStr([MyAddressField], " ") + 1)

.> .... Where do I put this?

You would put this in the Field: row of an empty column. It is a
"calculated field" which strips off the street number from your address,
leaving the street name, which is what you indicate you want to use to sort
your query.

In the criteria cell?
No.

Do I need to make 2 columns?

If you want to show the complete address (including the street number),
include the Address field in your query.

Do I need to enter this in " Address"collumn?

No, leave the Address column alone.

Do I need to make a new collumn and call it " Street name"?

Answered above.

So I now Have a collumn with "Address" in the field window
The sort is set for " ascending"

If you want to sort your query by the Name of the Street, use the Sort:
Ascending row in the column for the "calculated field", StreetName, and
remove the sort setting from the column containing your Address field.

What do i need to enter EXACTLY in the criteria window

Criteria are expressions which let you select the specific records which are
returned by the query. If you want all of the records in your table
returned by the query, leave the Criteria: row blank for all columns. If
you do want specific records returned by your query, please indicate which
records you want, as you did not mention this in your initial post.

 

[Cheryl Fischer]

Good to hear it, Jeff! Good luck with your project.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Cheryl

Thank you for sticking it out with me. I thought the way I decifered
it was wrong, because I kept getting syntax errors, And as I adjusted
according to the help window, It kept getting worse.
I Asked Tim for help because he explained things so idiot proof to me.
I now followed Steve's instruction stepby step. and the same syntax
errors appeared. So followed your instructions correctly after all.
As it turned out. As I clicked down, the table name was entered in its
field. When I finaly realized and corrected this, the query worked
exactly as I had hoped it would. And will make my spring workload
so much easier to accompish.
Again thank you so much for you patients and help

Jeff V.

You left quite a bit of space after your first question. My apologies for
not scrolling down.

StreetName: Mid([MyAddressField], InStr([MyAddressField], " ") +

1)
.> .... Where do I put this?

You would put this in the Field: row of an empty column. It is a
"calculated field" which strips off the street number from your address,
leaving the street name, which is what you indicate you want to use to sort
your query.

In the criteria cell?
No.

Do I need to make 2 columns?

If you want to show the complete address (including the street number),
include the Address field in your query.

Do I need to enter this in " Address"collumn?

No, leave the Address column alone.

Do I need to make a new collumn and call it " Street name"?

Answered above.

So I now Have a collumn with "Address" in the field window
The sort is set for " ascending"

If you want to sort your query by the Name of the Street, use the Sort:
Ascending row in the column for the "calculated field", StreetName, and
remove the sort setting from the column containing your Address field.

What do i need to enter EXACTLY in the criteria window

Criteria are expressions which let you select the specific records which are
returned by the query. If you want all of the records in your table
returned by the query, leave the Criteria: row blank for all columns. If
you do want specific records returned by your query, please indicate which
records you want, as you did not mention this in your initial post.