query to split data in a column into two separate columns

[Guest]

I am in need to do a conversion of data, specifically addresses, where the
current address is split into house# and street name columns.

Can this be done in a query, to leave the original table as is?

EX:
Table column "Address" = 123 SW Main St
query would split into Table column "House#" = 123
and Table column "Street" = SW Main St

Any help would be appreciated

 

[Guest]

A lot depends on how well your data fits the example below. If it ALWAYS
starts with numerical characters followed by a space, something like this
will work in a query:
House#: Trim(Val([Address]))

However what if the address is 123A SW Main St? That would leave the A
hanging. How about these then:

House#: Left([Address]),InStr([Address]," ")-1)
Street#: Mid([Address]),InStr([Address]," ")+1)

 

[Guest]

glad you brought up the exceptions. I thought I would start simple and
build. Here is why...

Some of the addresses are what we call interoffice addresses such as
"B123/R456"
or are PO Boxes (PO Box 1234), whereas those items need to end up in a
column all their own. The majority are like the simple solution.

thanks again

A lot depends on how well your data fits the example below. If it ALWAYS
starts with numerical characters followed by a space, something like this
will work in a query:
House#: Trim(Val([Address]))

However what if the address is 123A SW Main St? That would leave the A
hanging. How about these then:

House#: Left([Address]),InStr([Address]," ")-1)
Street#: Mid([Address]),InStr([Address]," ")+1)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am in need to do a conversion of data, specifically addresses, where the
current address is split into house# and street name columns.

Can this be done in a query, to leave the original table as is?

EX:
Table column "Address" = 123 SW Main St
query would split into Table column "House#" = 123
and Table column "Street" = SW Main St

Any help would be appreciated

 

[Guest]

You could use something like the IsNumeric function to see if the leading
character is numeric.

IsNumeric(Left("B123/R456",1)) would show false.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

glad you brought up the exceptions. I thought I would start simple and
build. Here is why...

Some of the addresses are what we call interoffice addresses such as
"B123/R456"
or are PO Boxes (PO Box 1234), whereas those items need to end up in a
column all their own. The majority are like the simple solution.

thanks again

A lot depends on how well your data fits the example below. If it ALWAYS
starts with numerical characters followed by a space, something like this
will work in a query:
House#: Trim(Val([Address]))

However what if the address is 123A SW Main St? That would leave the A
hanging. How about these then:

House#: Left([Address]),InStr([Address]," ")-1)
Street#: Mid([Address]),InStr([Address]," ")+1)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am in need to do a conversion of data, specifically addresses, where the
current address is split into house# and street name columns.

Can this be done in a query, to leave the original table as is?

EX:
Table column "Address" = 123 SW Main St
query would split into Table column "House#" = 123
and Table column "Street" = SW Main St

Any help would be appreciated

 

[Guest]

well I have tried it several different ways and it doesn't seem to work. I
am sorry to be a pain, but I need further assistance.

Expr2: [House]=Left([Address2],InStr([Address2]," ")-1)

Expr3: [Street]=Mid([Address2],InStr([Address2]," ")+1)

The above two statements open paramenter boxes, eek?

However when I do it like the below two statements -I believe from your
examples provided in early response, I get error message "expresssion you
entered has a function containing the wrong number of arguments". EEK, what
did I type wrong?

Expr2: [House]=Left([Address2]),InStr([Address2]," ")-1)

Expr3: [Street]=Mid([Address2]),InStr([Address2]," ")+1)

THANKS FOR ALL YOUR ASSISTANCE, I have read other posts similar to my
request and they are all suggesting what you suggested, but there is
obviously something I am not understanding. BTW, I am still using
Access97(***she says shyly***)

You could use something like the IsNumeric function to see if the leading
character is numeric.

IsNumeric(Left("B123/R456",1)) would show false.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

glad you brought up the exceptions. I thought I would start simple and
build. Here is why...

Some of the addresses are what we call interoffice addresses such as
"B123/R456"
or are PO Boxes (PO Box 1234), whereas those items need to end up in a
column all their own. The majority are like the simple solution.

thanks again

A lot depends on how well your data fits the example below. If it ALWAYS
starts with numerical characters followed by a space, something like this
will work in a query:
House#: Trim(Val([Address]))

However what if the address is 123A SW Main St? That would leave the A
hanging. How about these then:

House#: Left([Address]),InStr([Address]," ")-1)
Street#: Mid([Address]),InStr([Address]," ")+1)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I am in need to do a conversion of data, specifically addresses, where the
current address is split into house# and street name columns.

Can this be done in a query, to leave the original table as is?

EX:
Table column "Address" = 123 SW Main St
query would split into Table column "House#" = 123
and Table column "Street" = SW Main St

Any help would be appreciated

 

[John Spencer]

In the query grid

Field: House: Left([Address2],InStr([Address2]," ")-1)

Field: Street: Mid([Address2],InStr([Address2]," ")+1)

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

well I have tried it several different ways and it doesn't seem to work.
I
am sorry to be a pain, but I need further assistance.

Expr2: [House]=Left([Address2],InStr([Address2]," ")-1)

Expr3: [Street]=Mid([Address2],InStr([Address2]," ")+1)

The above two statements open paramenter boxes, eek?

However when I do it like the below two statements -I believe from your
examples provided in early response, I get error message "expresssion you
entered has a function containing the wrong number of arguments". EEK,
what
did I type wrong?

Expr2: [House]=Left([Address2]),InStr([Address2]," ")-1)

Expr3: [Street]=Mid([Address2]),InStr([Address2]," ")+1)

THANKS FOR ALL YOUR ASSISTANCE, I have read other posts similar to my
request and they are all suggesting what you suggested, but there is
obviously something I am not understanding. BTW, I am still using
Access97(***she says shyly***)

You could use something like the IsNumeric function to see if the leading
character is numeric.

IsNumeric(Left("B123/R456",1)) would show false.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

glad you brought up the exceptions. I thought I would start simple and
build. Here is why...

Some of the addresses are what we call interoffice addresses such as
"B123/R456"
or are PO Boxes (PO Box 1234), whereas those items need to end up in a
column all their own. The majority are like the simple solution.

thanks again


:

A lot depends on how well your data fits the example below. If it
ALWAYS
starts with numerical characters followed by a space, something like
this
will work in a query:
House#: Trim(Val([Address]))

However what if the address is 123A SW Main St? That would leave the
A
hanging. How about these then:

House#: Left([Address]),InStr([Address]," ")-1)
Street#: Mid([Address]),InStr([Address]," ")+1)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I am in need to do a conversion of data, specifically addresses,
where the
current address is split into house# and street name columns.

Can this be done in a query, to leave the original table as is?

EX:
Table column "Address" = 123 SW Main St
query would split into Table column "House#" = 123
and Table column "Street" = SW Main St

Any help would be appreciated

 

[Guest]

It should look like below in a query. The column heading will say House that
way.

House: Left([Address2],InStr([Address2]," ")-1)

Access 97 was my favorite version followed closely by Access 2.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

well I have tried it several different ways and it doesn't seem to work. I
am sorry to be a pain, but I need further assistance.

Expr2: [House]=Left([Address2],InStr([Address2]," ")-1)

Expr3: [Street]=Mid([Address2],InStr([Address2]," ")+1)

The above two statements open paramenter boxes, eek?

However when I do it like the below two statements -I believe from your
examples provided in early response, I get error message "expresssion you
entered has a function containing the wrong number of arguments". EEK, what
did I type wrong?

Expr2: [House]=Left([Address2]),InStr([Address2]," ")-1)

Expr3: [Street]=Mid([Address2]),InStr([Address2]," ")+1)

THANKS FOR ALL YOUR ASSISTANCE, I have read other posts similar to my
request and they are all suggesting what you suggested, but there is
obviously something I am not understanding. BTW, I am still using
Access97(***she says shyly***)

You could use something like the IsNumeric function to see if the leading
character is numeric.

IsNumeric(Left("B123/R456",1)) would show false.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

glad you brought up the exceptions. I thought I would start simple and
build. Here is why...

Some of the addresses are what we call interoffice addresses such as
"B123/R456"
or are PO Boxes (PO Box 1234), whereas those items need to end up in a
column all their own. The majority are like the simple solution.

thanks again


:

A lot depends on how well your data fits the example below. If it ALWAYS
starts with numerical characters followed by a space, something like this
will work in a query:
House#: Trim(Val([Address]))

However what if the address is 123A SW Main St? That would leave the A
hanging. How about these then:

House#: Left([Address]),InStr([Address]," ")-1)
Street#: Mid([Address]),InStr([Address]," ")+1)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I am in need to do a conversion of data, specifically addresses, where the
current address is split into house# and street name columns.

Can this be done in a query, to leave the original table as is?

EX:
Table column "Address" = 123 SW Main St
query would split into Table column "House#" = 123
and Table column "Street" = SW Main St

Any help would be appreciated

 

[Guest]

ok, now I get the message error:

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables.

Here are the statements as they are currently written
House: Left([Address2],InStr([Address2]," ")-1)
Street: Mid([Address2],InStr([Address2]," ")+1)

thanks as always

It should look like below in a query. The column heading will say House that
way.

House: Left([Address2],InStr([Address2]," ")-1)

Access 97 was my favorite version followed closely by Access 2.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

well I have tried it several different ways and it doesn't seem to work. I
am sorry to be a pain, but I need further assistance.

Expr2: [House]=Left([Address2],InStr([Address2]," ")-1)

Expr3: [Street]=Mid([Address2],InStr([Address2]," ")+1)

The above two statements open paramenter boxes, eek?

However when I do it like the below two statements -I believe from your
examples provided in early response, I get error message "expresssion you
entered has a function containing the wrong number of arguments". EEK, what
did I type wrong?

Expr2: [House]=Left([Address2]),InStr([Address2]," ")-1)

Expr3: [Street]=Mid([Address2]),InStr([Address2]," ")+1)

THANKS FOR ALL YOUR ASSISTANCE, I have read other posts similar to my
request and they are all suggesting what you suggested, but there is
obviously something I am not understanding. BTW, I am still using
Access97(***she says shyly***)

You could use something like the IsNumeric function to see if the leading
character is numeric.

IsNumeric(Left("B123/R456",1)) would show false.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

glad you brought up the exceptions. I thought I would start simple and
build. Here is why...

Some of the addresses are what we call interoffice addresses such as
"B123/R456"
or are PO Boxes (PO Box 1234), whereas those items need to end up in a
column all their own. The majority are like the simple solution.

thanks again


:

A lot depends on how well your data fits the example below. If it ALWAYS
starts with numerical characters followed by a space, something like this
will work in a query:
House#: Trim(Val([Address]))

However what if the address is 123A SW Main St? That would leave the A
hanging. How about these then:

House#: Left([Address]),InStr([Address]," ")-1)
Street#: Mid([Address]),InStr([Address]," ")+1)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I am in need to do a conversion of data, specifically addresses, where the
current address is split into house# and street name columns.

Can this be done in a query, to leave the original table as is?

EX:
Table column "Address" = 123 SW Main St
query would split into Table column "House#" = 123
and Table column "Street" = SW Main St

Any help would be appreciated

 

[John Spencer]

If Address2 is null or doesn't have a space in it you will get errors. I
would try something like

House: IIF(Address2 Like "?* *", Left(Address2,Instr(Address2, "
")-1),Address2)

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

ok, now I get the message error:

This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables.

Here are the statements as they are currently written
House: Left([Address2],InStr([Address2]," ")-1)
Street: Mid([Address2],InStr([Address2]," ")+1)

thanks as always

It should look like below in a query. The column heading will say House
that
way.

House: Left([Address2],InStr([Address2]," ")-1)

Access 97 was my favorite version followed closely by Access 2.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

well I have tried it several different ways and it doesn't seem to
work. I
am sorry to be a pain, but I need further assistance.

Expr2: [House]=Left([Address2],InStr([Address2]," ")-1)

Expr3: [Street]=Mid([Address2],InStr([Address2]," ")+1)

The above two statements open paramenter boxes, eek?

However when I do it like the below two statements -I believe from your
examples provided in early response, I get error message "expresssion
you
entered has a function containing the wrong number of arguments". EEK,
what
did I type wrong?

Expr2: [House]=Left([Address2]),InStr([Address2]," ")-1)

Expr3: [Street]=Mid([Address2]),InStr([Address2]," ")+1)

THANKS FOR ALL YOUR ASSISTANCE, I have read other posts similar to my
request and they are all suggesting what you suggested, but there is
obviously something I am not understanding. BTW, I am still using
Access97(***she says shyly***)

:

You could use something like the IsNumeric function to see if the
leading
character is numeric.

IsNumeric(Left("B123/R456",1)) would show false.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

glad you brought up the exceptions. I thought I would start simple
and
build. Here is why...

Some of the addresses are what we call interoffice addresses such
as
"B123/R456"
or are PO Boxes (PO Box 1234), whereas those items need to end up
in a
column all their own. The majority are like the simple solution.

thanks again


:

A lot depends on how well your data fits the example below. If it
ALWAYS
starts with numerical characters followed by a space, something
like this
will work in a query:
House#: Trim(Val([Address]))

However what if the address is 123A SW Main St? That would leave
the A
hanging. How about these then:

House#: Left([Address]),InStr([Address]," ")-1)
Street#: Mid([Address]),InStr([Address]," ")+1)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle
Builder.

:

I am in need to do a conversion of data, specifically
addresses, where the
current address is split into house# and street name columns.

Can this be done in a query, to leave the original table as is?

EX:
Table column "Address" = 123 SW Main St
query would split into Table column "House#" = 123
and Table column "Street" = SW Main St

Any help would be appreciated

 

[Guest]

But it opens a parameter box, rather than just pulling the data

If Address2 is null or doesn't have a space in it you will get errors. I
would try something like

House: IIF(Address2 Like "?* *", Left(Address2,Instr(Address2, "
")-1),Address2)

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

ok, now I get the message error:

This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables.

Here are the statements as they are currently written
House: Left([Address2],InStr([Address2]," ")-1)
Street: Mid([Address2],InStr([Address2]," ")+1)

thanks as always

It should look like below in a query. The column heading will say House
that
way.

House: Left([Address2],InStr([Address2]," ")-1)

Access 97 was my favorite version followed closely by Access 2.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

well I have tried it several different ways and it doesn't seem to
work. I
am sorry to be a pain, but I need further assistance.

Expr2: [House]=Left([Address2],InStr([Address2]," ")-1)

Expr3: [Street]=Mid([Address2],InStr([Address2]," ")+1)

The above two statements open paramenter boxes, eek?

However when I do it like the below two statements -I believe from your
examples provided in early response, I get error message "expresssion
you
entered has a function containing the wrong number of arguments". EEK,
what
did I type wrong?

Expr2: [House]=Left([Address2]),InStr([Address2]," ")-1)

Expr3: [Street]=Mid([Address2]),InStr([Address2]," ")+1)

THANKS FOR ALL YOUR ASSISTANCE, I have read other posts similar to my
request and they are all suggesting what you suggested, but there is
obviously something I am not understanding. BTW, I am still using
Access97(***she says shyly***)

:

You could use something like the IsNumeric function to see if the
leading
character is numeric.

IsNumeric(Left("B123/R456",1)) would show false.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

glad you brought up the exceptions. I thought I would start simple
and
build. Here is why...

Some of the addresses are what we call interoffice addresses such
as
"B123/R456"
or are PO Boxes (PO Box 1234), whereas those items need to end up
in a
column all their own. The majority are like the simple solution.

thanks again


:

A lot depends on how well your data fits the example below. If it
ALWAYS
starts with numerical characters followed by a space, something
like this
will work in a query:
House#: Trim(Val([Address]))

However what if the address is 123A SW Main St? That would leave
the A
hanging. How about these then:

House#: Left([Address]),InStr([Address]," ")-1)
Street#: Mid([Address]),InStr([Address]," ")+1)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle
Builder.

:

I am in need to do a conversion of data, specifically
addresses, where the
current address is split into house# and street name columns.

Can this be done in a query, to leave the original table as is?

EX:
Table column "Address" = 123 SW Main St
query would split into Table column "House#" = 123
and Table column "Street" = SW Main St

Any help would be appreciated

 

[John Spencer]

AND what does the Parameter box ask for? Usually if a parameter box opens,
it is because something is misspelled. For instance, is it Address2 or
Address_2 or Address 2?

One of those is probably the field name, the others are misspellings. OR
there is no address2 field in the tables that you are referencing.

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

But it opens a parameter box, rather than just pulling the data

If Address2 is null or doesn't have a space in it you will get errors. I
would try something like

House: IIF(Address2 Like "?* *", Left(Address2,Instr(Address2, "
")-1),Address2)

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

ok, now I get the message error:

This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables.

Here are the statements as they are currently written
House: Left([Address2],InStr([Address2]," ")-1)
Street: Mid([Address2],InStr([Address2]," ")+1)

thanks as always

:

It should look like below in a query. The column heading will say
House
that
way.

House: Left([Address2],InStr([Address2]," ")-1)

Access 97 was my favorite version followed closely by Access 2.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

well I have tried it several different ways and it doesn't seem to
work. I
am sorry to be a pain, but I need further assistance.

Expr2: [House]=Left([Address2],InStr([Address2]," ")-1)

Expr3: [Street]=Mid([Address2],InStr([Address2]," ")+1)

The above two statements open paramenter boxes, eek?

However when I do it like the below two statements -I believe from
your
examples provided in early response, I get error message
"expresssion
you
entered has a function containing the wrong number of arguments".
EEK,
what
did I type wrong?

Expr2: [House]=Left([Address2]),InStr([Address2]," ")-1)

Expr3: [Street]=Mid([Address2]),InStr([Address2]," ")+1)

THANKS FOR ALL YOUR ASSISTANCE, I have read other posts similar to
my
request and they are all suggesting what you suggested, but there is
obviously something I am not understanding. BTW, I am still using
Access97(***she says shyly***)

:

You could use something like the IsNumeric function to see if the
leading
character is numeric.

IsNumeric(Left("B123/R456",1)) would show false.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

glad you brought up the exceptions. I thought I would start
simple
and
build. Here is why...

Some of the addresses are what we call interoffice addresses
such
as
"B123/R456"
or are PO Boxes (PO Box 1234), whereas those items need to end
up
in a
column all their own. The majority are like the simple
solution.

thanks again


:

A lot depends on how well your data fits the example below. If
it
ALWAYS
starts with numerical characters followed by a space,
something
like this
will work in a query:
House#: Trim(Val([Address]))

However what if the address is 123A SW Main St? That would
leave
the A
hanging. How about these then:

House#: Left([Address]),InStr([Address]," ")-1)
Street#: Mid([Address]),InStr([Address]," ")+1)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle
Builder.

:

I am in need to do a conversion of data, specifically
addresses, where the
current address is split into house# and street name
columns.

Can this be done in a query, to leave the original table as
is?

EX:
Table column "Address" = 123 SW Main St
query would split into Table column "House#" = 123
and Table column "Street" = SW Main St

Any help would be appreciated