Expression

[rciolkosz]

What is the expression to separate street number from street name?

 

[Allen Browne]

What is the expression to separate street number from street name?

There's not really a simple answer to that question.

If the field is called AddressLine1, you could try this in the Field row in
query design:
IIf([AddressLine1] Is Null, Null, Val([AddressLine1]))

However this would fail with lots of addresess, such as:
2 / 45 Main St
Unit 2, 45 Main St
Lot 2 Main St
Cnr Main and Cross Sts
First Floor, 45 Main St
and heaps more.

 

[rciolkosz]

how about an expression just for the text part of the address and not the
numeric

What is the expression to separate street number from street name?

There's not really a simple answer to that question.

If the field is called AddressLine1, you could try this in the Field row in
query design:
IIf([AddressLine1] Is Null, Null, Val([AddressLine1]))

However this would fail with lots of addresess, such as:
2 / 45 Main St
Unit 2, 45 Main St
Lot 2 Main St
Cnr Main and Cross Sts
First Floor, 45 Main St
and heaps more.

 

[Al Campagna]

rciolkosz,
If all the address strings (ex. name Addr1) have a legitimate number
first, and then a space...
ex.
67 Main St
12 North Rd
134 Elm St
then
= Left(Addr1, InStr(Addr1," ")-1)
should do it.

**Any address string that does not adhere to the strict format above, will
fail to deliver a legitimate result.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Allen Browne]

Aftern you get the numeric part, you could use:
Trim(Mid([AddressLine1], Len([AddressNumber])+1))
adding suitable criteria or Nz() or IIf() expressions to avoid issues with
nulls.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

how about an expression just for the text part of the address and not the
numeric

What is the expression to separate street number from street name?

There's not really a simple answer to that question.

If the field is called AddressLine1, you could try this in the Field row
in
query design:
IIf([AddressLine1] Is Null, Null, Val([AddressLine1]))

 

[vbasean]

you'll have to build a custom function or an inline function on an update
query.

I've done this before for various reasons (trying to group areas by street
etcetera) the number is separated from the street by a space (for the most
part), you have to concider Post Office Box address "PO Box, P.O. Box, P O
Box" (this in itself is a topic, validating PO box address to keep them
conformed to a standard.) So making exceptions for that.

Update Function could be similar to this:
StreetName = Right([Address Field], len([Address Field]) - instr(1, [Address
Field], " "))
StreetNumber = Trim(Left([Address Field], instr(1, [Address Field], " ")))

Criteria:
is not like 'PO *'
is not like 'P O*'
is not like 'P.O*'
is not like 'P. O*'

 

[rciolkosz]

Well that isolated the number portion how about isolating the text portion

 

[Al Campagna]

rciolkosz,
= Mid(Addr1, InStr(Addr1," ") + 1)
I repeat...--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."