Parse Field Alphabetically

[Pincus]

I have an address field that conatins street intersections like: Main St &
Dean St

Each record has an "&" between the two streets. I would like to alphabetize
these street intersections so in this example Dean St. would always come
before Main St. Any ideas?

Thanks

 

[John Spencer]

ORDER BY IIF(LEFT([address],Instr(1,[Address],"&")-1) >
Trim(Mid([Address],Instr(1,[Address],"&")+1))
, Trim(Mid([Address],Instr(1,[Address],"&")+1)), [Address])


In design view add a calculated field and sort by the calculated field
Field: SpecialSort: IIF(LEFT([address],Instr(1,[Address],"&")-1) >
Trim(Mid([Address],Instr(1,[Address],"&")+1))
, Trim(Mid([Address],Instr(1,[Address],"&")+1)), [Address])

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Pincus]

Thanks for your suggestion. However the only street names that get pulled in
are the ones after the "&" (ex. Dean Street)

How can I pull the entire intersection in (Dean St & Main St)?

ORDER BY IIF(LEFT([address],Instr(1,[Address],"&")-1) >
Trim(Mid([Address],Instr(1,[Address],"&")+1))
, Trim(Mid([Address],Instr(1,[Address],"&")+1)), [Address])

In design view add a calculated field and sort by the calculated field
Field: SpecialSort: IIF(LEFT([address],Instr(1,[Address],"&")-1) >
Trim(Mid([Address],Instr(1,[Address],"&")+1))
, Trim(Mid([Address],Instr(1,[Address],"&")+1)), [Address])

I have an address field that conatins street intersections like: Main St &
Dean St

[quoted text clipped - 5 lines]

Thanks

 

[John Spencer]

Perhaps I don't understand what you are trying to do. I thought that you
wanted to order your records by the address and whichever street came first
alphabetically would be use to impose the order.

Are you saying you want to change the content of the field from
Main & Dean
to
Dean & Main?

If so, do you want to permanently save the change.

I think the easiest way to handle this would be to write a custom vba
function. Untested AIRCODE follows. Cut and paste this into a VBA module,
save the module (with some name other than fAlphabetizeStreets) and call it
from your query

Field: FixAddress: fAlphabetizeStreets([Your Address Field])

Public Function fAlphabetizeStreets(strIN)
Dim vParts As Variant

If Len(strIN & "") = 0 Then
fAlphabetizeStreets = strIN
ElseIf InStr(1, strIN, "&") = 0 Then
fAlphabetizeStreets = strIN
Else
vParts = Split(strIN, "&")
If Trim(vParts(0)) < Trim(vParts(1)) Then
fAlphabetizeStreets = strIN
Else
fAlphabetizeStreets = Trim(vParts(1)) & " & " & Trim(vParts(0))
End If
End If

End Function



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks for your suggestion. However the only street names that get pulled
in
are the ones after the "&" (ex. Dean Street)

How can I pull the entire intersection in (Dean St & Main St)?

ORDER BY IIF(LEFT([address],Instr(1,[Address],"&")-1) >
Trim(Mid([Address],Instr(1,[Address],"&")+1))
, Trim(Mid([Address],Instr(1,[Address],"&")+1)), [Address])

In design view add a calculated field and sort by the calculated field
Field: SpecialSort: IIF(LEFT([address],Instr(1,[Address],"&")-1) >
Trim(Mid([Address],Instr(1,[Address],"&")+1))
, Trim(Mid([Address],Instr(1,[Address],"&")+1)), [Address])

I have an address field that conatins street intersections like: Main St
&
Dean St

[quoted text clipped - 5 lines]

Thanks