Expression

A

Allen Browne

rciolkosz said:
What is the expression to separate street number from street name?
Click to expand...

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.
 
R

rciolkosz

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

Allen Browne said:
rciolkosz said:
What is the expression to separate street number from street name?
Click to expand...

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.
Click to expand...
 
A

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."
 
A

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.

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

Allen Browne said:
rciolkosz said:
What is the expression to separate street number from street name?
Click to expand...

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]))
Click to expand...
Click to expand...
 
V

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*'
 
Top