Seperating number from address

[coreyc]

I am a newbie and I need a query to take the address
field of my access 2000 database and seperate the street
# from the street name into 2 seperate fields.

Example:
123 Dallas St.

Query would seperare one field as 123 and another field
as Dallas St.

 

[Douglas J. Steele]

For the example you posted, the following would work:

StreetNumber: Left$([Address], InStr([Address], " ") - 1)
StreetName: Mid$([Address], InStr([Address], " ") + 1)

 

[John Spencer (MVP)]

It depends. Are ALL the addresses numbers, then a space, then the street? If
so, then you can use an update query that looks something like:

UPDATE YourTable
SET NumberField = Left(AddressField,InStr(1,AddressField," ") -1)
StreetField = Mid(AddressField,Instr(1,Addressfield," ") +1)
WHERE AddressField Like "#* *"

If you are doing this in the query grid and cannot figure out how to do this in
the query's SQL window, then post back and I will try to describe the setup to
do this using the grid.

 

[coreyc]

Thanks very much!

Would you mind describing the setup...as I am not too
familiar with database programming.

Corey

 

[John Spencer (MVP)]

Open the query grid and bring in your table, which should have the new fields in it.

Select Query: Update query from the Menu

Set up the following columns

Field: AddressField
Table: YourTableName
Update To: <Leave blank>
Criteria: Like "#* *"

Field: NumberField
Table: YourTableName
Update To: Left(AddressField,InStr(1,AddressField," ") -1)

Field: StreetField
Table: YourTableName
Update To: Mid(AddressField,Instr(1,Addressfield," ") +1)

Select Query: Run from the menu

If All you really need is to use the data but not create new fields in the table
then just add some calculated columns to your standard Select query, instead of
making an UPDATE query.

Field: NumberField: Left(AddressField,InStr(1,AddressField," ") -1)
Table: <Leave blank>

Field: StreetField: Mid(AddressField,Instr(1,Addressfield," ") +1)
Table: <Leave blank>

 

[coreyc]

Thanks! But I am having a little trouble.

When I perform either of your suggestions and run the
query, it asks me for parameter values. I am not sure
what it is asking for and anything I type, it displays
all columns with the data I type. Also, I assume instead
of actually calling AddressField, I call the actual name
of the column in my table, which is Owner Address.

If it helps, I am just wanting to open a query and it
display 1st column with the # and the second column with
the Street. I will use this to either print out or sort
by address name.

I appreciate all your help...I am starting to get it..

Corey

-----Original Message-----
Open the query grid and bring in your table, which

should have the new fields in it.

 

[John Spencer (MVP)]

Yes, you must use YOUR fieldnames and tablenames. I am sorry, but me psychic
powers have been overused and I am no longer able to extract this information
thru the aether.

Have you already created fields in the table to hold the number and street
portions? Or do you just want to separate them out?

If the latter then use the setup I gave you in the second part of the query. If
that still doesn't work, then
Copy the SQL of the query into your message and tell us WHAT the parameter
query is asking for.


(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

 

[coreyc]

After trying both ways, I stuck with your first
suggestion of creating new columns in the table labeled
StreetField and NumberField.

Performing your table update query, it filled the
NumberField with Owner and the StreetField with Address.

The complete address field is labeled as Owner Address.

Here is my SQL view:
UPDATE AAAA SET AAAA.NumberField = Left("Owner
Address",InStr(1,"Owner Address"," ")-1),
AAAA.StreetField = Mid("Owner Address",InStr(1,"Owner
Address"," ")+1);

 

[John Spencer (MVP)]

When you entered the fields into the expression using the query grid, Access
didn't recognize them as field names and converted them to strings.

Try changing the SQL to:

UPDATE AAAA SET AAAA.NumberField = Left([Owner Address],InStr(1,[Owner
Address]," ")-1),
AAAA.StreetField = Mid([Owner Address],InStr(1,[Owner Address]," ")+1);

OR when you enter into the grid, use brackets around the field name.

Update To: Mid([Owner Address],Instr(1,[Owner Address]," ") +1)

To be completely safe, add the tablename to the field name as below.

Update To: Mid([AAAA].[Owner Address],Instr(1,[AAAA].[Owner Address]," ") +1)

Thanks for posting the actual SQL statement. It makes it a LOT easier to
diagnose the probable cause and suggest a solution.

 

[coreyc]

Thanks! I am not sure what is wrong now...I changed the
SQL to:

UPDATE AAAA SET AAAA.NumberField = Left([Owner
Address],InStr(1,[Owner Address]," ")-1),
AAAA.StreetField = Mid([Owner Address],InStr(1,[Owner
Address]," ")+1);

When I ran it, the Owner Address columns in the table was
erased and replaced with astericks all the way down...and
same as the StreetField and NumberField columns. The
gives an error of low memory or disk space....and this on
on a server with 2GB memory and 40 GB free space. The DB
is 270MB.

I also tried your suggestions with the Query grid, and
got the same result.

Again, sorry to be a pest!! but once i get this...I will
be able to use it every month we need to modify the new
DBs...it will help a whole lot.

Corey

-----Original Message-----
When you entered the fields into the expression using the query grid, Access
didn't recognize them as field names and converted them to strings.

Try changing the SQL to:

UPDATE AAAA SET AAAA.NumberField = Left([Owner Address],InStr(1,[Owner
Address]," ")-1),
AAAA.StreetField = Mid([Owner Address],InStr(1,[Owner Address]," ")+1);

OR when you enter into the grid, use brackets around the field name.

Update To: Mid([Owner Address],Instr(1,[Owner Address]," ") +1)

To be completely safe, add the tablename to the field name as below.

Update To: Mid([AAAA].[Owner Address],Instr(1,[AAAA]. [Owner Address]," ") +1)

Thanks for posting the actual SQL statement. It makes it a LOT easier to
diagnose the probable cause and suggest a solution.

After trying both ways, I stuck with your first
suggestion of creating new columns in the table labeled
StreetField and NumberField.

Performing your table update query, it filled the
NumberField with Owner and the StreetField with Address.

The complete address field is labeled as Owner Address.

Here is my SQL view:
UPDATE AAAA SET AAAA.NumberField = Left("Owner
Address",InStr(1,"Owner Address"," ")-1),
AAAA.StreetField = Mid("Owner Address",InStr(1,"Owner
Address"," ")+1);
am
sorry, but me psychic the
number and street (1,Addressfield," ")
+1)

.

 

[John Spencer (MVP)]

The only thing i can think of is that some of the fields do not have a space in
them. So try using a where clause

UPDATE AAAA
SET AAAA.NumberField =
Left([Owner Address],InStr(1,[Owner Address]," ")-1),
AAAA.StreetField =
Mid([Owner Address],InStr(1,[Owner Address]," ")+1)
WHERE [Owner Address] Like "?* *"

Beyond that possibility I am stuck.

Thanks! I am not sure what is wrong now...I changed the
SQL to:

UPDATE AAAA SET AAAA.NumberField = Left([Owner
Address],InStr(1,[Owner Address]," ")-1),
AAAA.StreetField = Mid([Owner Address],InStr(1,[Owner
Address]," ")+1);

When I ran it, the Owner Address columns in the table was
erased and replaced with astericks all the way down...and
same as the StreetField and NumberField columns. The
gives an error of low memory or disk space....and this on
on a server with 2GB memory and 40 GB free space. The DB
is 270MB.

I also tried your suggestions with the Query grid, and
got the same result.

Again, sorry to be a pest!! but once i get this...I will
be able to use it every month we need to modify the new
DBs...it will help a whole lot.

Corey

-----Original Message-----
When you entered the fields into the expression using the query grid, Access
didn't recognize them as field names and converted them to strings.

Try changing the SQL to:

UPDATE AAAA SET AAAA.NumberField = Left([Owner Address],InStr(1,[Owner
Address]," ")-1),
AAAA.StreetField = Mid([Owner Address],InStr(1,[Owner Address]," ")+1);

OR when you enter into the grid, use brackets around the field name.

Update To: Mid([Owner Address],Instr(1,[Owner Address]," ") +1)

To be completely safe, add the tablename to the field name as below.

Update To: Mid([AAAA].[Owner Address],Instr(1,[AAAA]. [Owner Address]," ") +1)

Thanks for posting the actual SQL statement. It makes it a LOT easier to
diagnose the probable cause and suggest a solution.

After trying both ways, I stuck with your first
suggestion of creating new columns in the table labeled
StreetField and NumberField.

Performing your table update query, it filled the
NumberField with Owner and the StreetField with Address.

The complete address field is labeled as Owner Address.

Here is my SQL view:
UPDATE AAAA SET AAAA.NumberField = Left("Owner
Address",InStr(1,"Owner Address"," ")-1),
AAAA.StreetField = Mid("Owner Address",InStr(1,"Owner
Address"," ")+1);

-----Original Message-----

Yes, you must use YOUR fieldnames and tablenames. I am
sorry, but me psychic
powers have been overused and I am no longer able to
extract this information
thru the aether.

Have you already created fields in the table to hold the
number and street
portions? Or do you just want to separate them out?

If the latter then use the setup I gave you in the
second part of the query. If
that still doesn't work, then
Copy the SQL of the query into your message and tell
us WHAT the parameter
query is asking for.


(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


coreyc wrote:

Thanks! But I am having a little trouble.

When I perform either of your suggestions and run the
query, it asks me for parameter values. I am not sure
what it is asking for and anything I type, it displays
all columns with the data I type. Also, I assume
instead
of actually calling AddressField, I call the actual
name
of the column in my table, which is Owner Address.

If it helps, I am just wanting to open a query and it
display 1st column with the # and the second column
with
the Street. I will use this to either print out or
sort
by address name.

I appreciate all your help...I am starting to get it..

Corey
-----Original Message-----
Open the query grid and bring in your table, which
should have the new fields in it.

Select Query: Update query from the Menu

Set up the following columns

Field: AddressField
Table: YourTableName
Update To: <Leave blank>
Criteria: Like "#* *"

Field: NumberField
Table: YourTableName
Update To: Left(AddressField,InStr
(1,AddressField," ") -
1)

Field: StreetField
Table: YourTableName
Update To: Mid(AddressField,Instr (1,Addressfield," ")
+1)

Select Query: Run from the menu

If All you really need is to use the data but not
create
new fields in the table
then just add some calculated columns to your standard
Select query, instead of
making an UPDATE query.

Field: NumberField: Left(AddressField,InStr
(1,AddressField," ") -1)
Table: <Leave blank>

Field: StreetField: Mid(AddressField,Instr
(1,Addressfield," ") +1)
Table: <Leave blank>


:

It depends. Are ALL the addresses numbers, then a
space, then the street? If
so, then you can use an update query that looks
something like:

UPDATE YourTable
SET NumberField = Left(AddressField,InStr
(1,AddressField," ") -1)
StreetField = Mid(AddressField,Instr
(1,Addressfield," ") +1)
WHERE AddressField Like "#* *"

If you are doing this in the query grid and cannot
figure out how to do this in
the query's SQL window, then post back and I will
try
to describe the setup to
do this using the grid.

coreyc wrote:

I am a newbie and I need a query to take the
address
field of my access 2000 database and seperate the
street
# from the street name into 2 seperate fields.

Example:
123 Dallas St.

Query would seperare one field as 123 and another
field
as Dallas St.
.

.

.

 

[coreyc]

That did it!

Thanks for your work...I am very greatful...

Corey

-----Original Message-----
The only thing i can think of is that some of the fields do not have a space in
them. So try using a where clause

UPDATE AAAA
SET AAAA.NumberField =
Left([Owner Address],InStr(1,[Owner Address]," ")-1),
AAAA.StreetField =
Mid([Owner Address],InStr(1,[Owner Address]," ")+1)
WHERE [Owner Address] Like "?* *"

Beyond that possibility I am stuck.

Thanks! I am not sure what is wrong now...I changed the
SQL to:

UPDATE AAAA SET AAAA.NumberField = Left([Owner
Address],InStr(1,[Owner Address]," ")-1),
AAAA.StreetField = Mid([Owner Address],InStr(1,[Owner
Address]," ")+1);

When I ran it, the Owner Address columns in the table was
erased and replaced with astericks all the way down...and
same as the StreetField and NumberField columns. The
gives an error of low memory or disk space....and this on
on a server with 2GB memory and 40 GB free space. The DB
is 270MB.

I also tried your suggestions with the Query grid, and
got the same result.

Again, sorry to be a pest!! but once i get this...I will
be able to use it every month we need to modify the new
DBs...it will help a whole lot.

Corey

-----Original Message-----
When you entered the fields into the expression using the query grid, Access
didn't recognize them as field names and converted

them
to strings.

Try changing the SQL to:

UPDATE AAAA SET AAAA.NumberField = Left([Owner Address],InStr(1,[Owner
Address]," ")-1),
AAAA.StreetField = Mid([Owner Address],InStr(1,[Owner Address]," ")+1);

OR when you enter into the grid, use brackets around

the
field name.

Update To: Mid([Owner Address],Instr(1,[Owner Address]," ") +1)

To be completely safe, add the tablename to the field name as below.

Update To: Mid([AAAA].[Owner Address],Instr(1,[AAAA]. [Owner Address]," ") +1)

Thanks for posting the actual SQL statement. It makes it a LOT easier to
diagnose the probable cause and suggest a solution.

coreyc wrote:

After trying both ways, I stuck with your first
suggestion of creating new columns in the table labeled
StreetField and NumberField.

Performing your table update query, it filled the
NumberField with Owner and the StreetField with Address.

The complete address field is labeled as Owner Address.

Here is my SQL view:
UPDATE AAAA SET AAAA.NumberField = Left("Owner
Address",InStr(1,"Owner Address"," ")-1),
AAAA.StreetField = Mid("Owner Address",InStr (1,"Owner
Address"," ")+1);

-----Original Message-----

Yes, you must use YOUR fieldnames and tablenames.

I
am

sorry, but me psychic
powers have been overused and I am no longer able to
extract this information
thru the aether.

Have you already created fields in the table to

hold
the

number and street
portions? Or do you just want to separate them out?

If the latter then use the setup I gave you in the
second part of the query. If
that still doesn't work, then
Copy the SQL of the query into your message and tell
us WHAT the parameter
query is asking for.


(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


coreyc wrote:

Thanks! But I am having a little trouble.

When I perform either of your suggestions and run the
query, it asks me for parameter values. I am not sure
what it is asking for and anything I type, it displays
all columns with the data I type. Also, I assume
instead
of actually calling AddressField, I call the actual
name
of the column in my table, which is Owner Address.

If it helps, I am just wanting to open a query

and
it

display 1st column with the # and the second column
with
the Street. I will use this to either print out or
sort
by address name.

I appreciate all your help...I am starting to get it..

Corey
-----Original Message-----
Open the query grid and bring in your table, which
should have the new fields in it.

Select Query: Update query from the Menu

Set up the following columns

Field: AddressField
Table: YourTableName
Update To: <Leave blank>
Criteria: Like "#* *"

Field: NumberField
Table: YourTableName
Update To: Left(AddressField,InStr
(1,AddressField," ") -
1)

Field: StreetField
Table: YourTableName
Update To: Mid(AddressField,Instr (1,Addressfield," ")
+1)

Select Query: Run from the menu

If All you really need is to use the data but not
create
new fields in the table
then just add some calculated columns to your standard
Select query, instead of
making an UPDATE query.

Field: NumberField: Left(AddressField,InStr
(1,AddressField," ") -1)
Table: <Leave blank>

Field: StreetField: Mid(AddressField,Instr
(1,Addressfield," ") +1)
Table: <Leave blank>


:

It depends. Are ALL the addresses numbers,

then
a

space, then the street? If
so, then you can use an update query that looks
something like:

UPDATE YourTable
SET NumberField = Left(AddressField,InStr
(1,AddressField," ") -1)
StreetField = Mid(AddressField,Instr
(1,Addressfield," ") +1)
WHERE AddressField Like "#* *"

If you are doing this in the query grid and cannot
figure out how to do this in
the query's SQL window, then post back and I will
try
to describe the setup to
do this using the grid.

coreyc wrote:

I am a newbie and I need a query to take the
address
field of my access 2000 database and

seperate
the

street
# from the street name into 2 seperate fields.

Example:
123 Dallas St.

Query would seperare one field as 123 and another
field
as Dallas St.
.

.

.

.

 

[John Spencer (MVP)]

Glad that solved it, cause I was out of ideas.

That did it!

Thanks for your work...I am very greatful...

Corey

-----Original Message-----
The only thing i can think of is that some of the fields do not have a space in
them. So try using a where clause

UPDATE AAAA
SET AAAA.NumberField =
Left([Owner Address],InStr(1,[Owner Address]," ")-1),
AAAA.StreetField =
Mid([Owner Address],InStr(1,[Owner Address]," ")+1)
WHERE [Owner Address] Like "?* *"

Beyond that possibility I am stuck.

Thanks! I am not sure what is wrong now...I changed the
SQL to:

UPDATE AAAA SET AAAA.NumberField = Left([Owner
Address],InStr(1,[Owner Address]," ")-1),
AAAA.StreetField = Mid([Owner Address],InStr(1,[Owner
Address]," ")+1);

When I ran it, the Owner Address columns in the table was
erased and replaced with astericks all the way down...and
same as the StreetField and NumberField columns. The
gives an error of low memory or disk space....and this on
on a server with 2GB memory and 40 GB free space. The DB
is 270MB.

I also tried your suggestions with the Query grid, and
got the same result.

Again, sorry to be a pest!! but once i get this...I will
be able to use it every month we need to modify the new
DBs...it will help a whole lot.

Corey

-----Original Message-----
When you entered the fields into the expression using
the query grid, Access
didn't recognize them as field names and converted them
to strings.

Try changing the SQL to:

UPDATE AAAA SET AAAA.NumberField = Left([Owner
Address],InStr(1,[Owner
Address]," ")-1),
AAAA.StreetField = Mid([Owner Address],InStr(1,[Owner
Address]," ")+1);

OR when you enter into the grid, use brackets around the
field name.

Update To: Mid([Owner Address],Instr(1,[Owner
Address]," ") +1)

To be completely safe, add the tablename to the field
name as below.

Update To: Mid([AAAA].[Owner Address],Instr(1,[AAAA].
[Owner Address]," ") +1)

Thanks for posting the actual SQL statement. It makes
it a LOT easier to
diagnose the probable cause and suggest a solution.

coreyc wrote:

After trying both ways, I stuck with your first
suggestion of creating new columns in the table labeled
StreetField and NumberField.

Performing your table update query, it filled the
NumberField with Owner and the StreetField with
Address.

The complete address field is labeled as Owner Address.

Here is my SQL view:
UPDATE AAAA SET AAAA.NumberField = Left("Owner
Address",InStr(1,"Owner Address"," ")-1),
AAAA.StreetField = Mid("Owner Address",InStr (1,"Owner
Address"," ")+1);

-----Original Message-----

Yes, you must use YOUR fieldnames and tablenames. I
am
sorry, but me psychic
powers have been overused and I am no longer able to
extract this information
thru the aether.

Have you already created fields in the table to hold
the
number and street
portions? Or do you just want to separate them out?

If the latter then use the setup I gave you in the
second part of the query. If
that still doesn't work, then
Copy the SQL of the query into your message and tell
us WHAT the parameter
query is asking for.


(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


coreyc wrote:

Thanks! But I am having a little trouble.

When I perform either of your suggestions and run
the
query, it asks me for parameter values. I am not
sure
what it is asking for and anything I type, it
displays
all columns with the data I type. Also, I assume
instead
of actually calling AddressField, I call the actual
name
of the column in my table, which is Owner Address.

If it helps, I am just wanting to open a query and
it
display 1st column with the # and the second column
with
the Street. I will use this to either print out or
sort
by address name.

I appreciate all your help...I am starting to get
it..

Corey
-----Original Message-----
Open the query grid and bring in your table, which
should have the new fields in it.

Select Query: Update query from the Menu

Set up the following columns

Field: AddressField
Table: YourTableName
Update To: <Leave blank>
Criteria: Like "#* *"

Field: NumberField
Table: YourTableName
Update To: Left(AddressField,InStr
(1,AddressField," ") -
1)

Field: StreetField
Table: YourTableName
Update To: Mid(AddressField,Instr
(1,Addressfield," ")
+1)

Select Query: Run from the menu

If All you really need is to use the data but not
create
new fields in the table
then just add some calculated columns to your
standard
Select query, instead of
making an UPDATE query.

Field: NumberField: Left(AddressField,InStr
(1,AddressField," ") -1)
Table: <Leave blank>

Field: StreetField: Mid(AddressField,Instr
(1,Addressfield," ") +1)
Table: <Leave blank>


:

It depends. Are ALL the addresses numbers, then
a
space, then the street? If
so, then you can use an update query that looks
something like:

UPDATE YourTable
SET NumberField = Left(AddressField,InStr
(1,AddressField," ") -1)
StreetField = Mid(AddressField,Instr
(1,Addressfield," ") +1)
WHERE AddressField Like "#* *"

If you are doing this in the query grid and
cannot
figure out how to do this in
the query's SQL window, then post back and I will
try
to describe the setup to
do this using the grid.

coreyc wrote:

I am a newbie and I need a query to take the
address
field of my access 2000 database and seperate
the
street
# from the street name into 2 seperate fields.

Example:
123 Dallas St.

Query would seperare one field as 123 and
another
field
as Dallas St.
.

.

.

.