Tim F, Please help with address query

[Iwilfix]

Tim

I have an address field (called address)
It contains street numbers and street names.
(45 Elm St. 584 Spring Rd. 34 Center Ave. Etc)
I need a query that puts the street names in alphabetical order,
but should still display its associated numbers.

I have gotten several responses to this question in the group.
But they just do not work. I probably did not understand what I was
told to do. I just don't know where the information is to be entered.
I need step by step instructions. You helped me a few times before
with great results.
Thanks again, Jeff V.

 

[Steve Schapel]

Jeff,

Hope you don't mind a non-Tim jumping in here. Personally I don't think
I or Tim could explain it better than Cheryl did in your earlier thread
on this question. But anyway, try this...

1. Make a new query based on your table.
2. Put the Address field in the first column of the query design grid.
3. In the Field row of the next column of the query design grid, type
the following...
StreetName: Mid([MyAddressField],InStr([MyAddressField]," ")+1)
4. In the Sort row of this column, select Ascending.
5. In the Show row of this column, untick the checkbox.
6. Run the query.
7. Post back here and announce your success.
8. Post back in the other thread and say thank you to Cheryl

As mentioned by Cheryl, this assumes that *all* entries in you Address
field follow the pattern: number-space-street.

 

[Cheryl Fischer]

All expert assistance cheerfully accepted! <smile>

Perhaps it's the hour, but did Tim (Ferguson?) have a thread going on this
subject with the OP? If so, I completely missed it. Maybe I should just
say G'Nite, now?

Thanks.

--

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

Jeff,

Hope you don't mind a non-Tim jumping in here. Personally I don't think
I or Tim could explain it better than Cheryl did in your earlier thread
on this question. But anyway, try this...

1. Make a new query based on your table.
2. Put the Address field in the first column of the query design grid.
3. In the Field row of the next column of the query design grid, type
the following...
StreetName: Mid([MyAddressField],InStr([MyAddressField]," ")+1)
4. In the Sort row of this column, select Ascending.
5. In the Show row of this column, untick the checkbox.
6. Run the query.
7. Post back here and announce your success.
8. Post back in the other thread and say thank you to Cheryl

As mentioned by Cheryl, this assumes that *all* entries in you Address
field follow the pattern: number-space-street.

--
Steve Schapel, Microsoft Access MVP

Tim

I have an address field (called address)
It contains street numbers and street names.
(45 Elm St. 584 Spring Rd. 34 Center Ave. Etc)
I need a query that puts the street names in alphabetical order,
but should still display its associated numbers.

I have gotten several responses to this question in the group.
But they just do not work. I probably did not understand what I was
told to do. I just don't know where the information is to be entered.
I need step by step instructions. You helped me a few times before
with great results.
Thanks again, Jeff V.

 

[Iwilfix]

Steve

Thank you for your simple instructions.
As I followed them, it turns out that I did
do exactly what Cheryl had instucted me to do previous.
but syntax errors kept appearing and i was just getting frustrated.
As I explained to Cheryl, when I clicked down to he column.
the table name was entered in its box, and thats where
the problem was of course.(only tookme 2 hours to figure out)
But now it works perfectly.
Thank you so much.
Jeff V.
( and I did of course thank Cheryl as well )

 

[Steve Schapel]

Great news, Jeff!

 

[Tim Ferguson]

Perhaps it's the hour, but did Tim (Ferguson?) have a thread going on
this subject with the OP?

Don't recognise it! Then again, my memory is not what it was... <g>

Anyway it seems he has it fixed so that's alright then.

B Wishes


Tim F