Separate data in one field into separate "lines" or fields

[Guest]

I have a database with a linked table, so I am not able to change the table.
But one of the fields (Payee Address) contains all the information (separated
by spaces) that would normally be found in multiple fields (ex: "John Smith
Trustee FBO John Smith 123 Main Street Anytown, US 12345").
This data is tied to an account number.

I've tried using the trim function, to no avail. Is there a function that I
can create in a query so that the original table is not changed, but the data
I need (in real time) can be broken out into separate fields? I hope I'm
making sense. I've looked at this too long!

Thanks for your help!! You guys are the best!!

 

[ti976]

It's possible if the data in the field is structured consistently, ie,
2 word names only, ex:
John Smith vs John D. Smith. If the data is of variable word length,
then it's probably not possible since spaces are the only punctuation
mark being used. Also you would need a custom function that will
'parse' out the spaces to break up the text values.
Probably not worth the effort.

 

[Guest]

As "ti976" indicates, if the field is structured consistently it is possible.
I suspect that your task will be complicated by having variable word length.
However, with that said, here is a starting point that you might be able to
build upon. First, create a new module. Copy and paste the following function
into this new module:

Public Function SplitName(strFullName As String, _
intElement As Integer) As String
On Error Resume Next
Dim strResult() As String

strResult = Split([strFullName], Chr(32))

SplitName = strResult(intElement)

End Function


Here is a SQL statement for a query that breaks the first three values, and
the zip code, into separate fileds for a table named "tblTest" and a field
named "NameAddressEtc":

SELECT tblTest.NameAddressEtc,
SplitName([NameAddressEtc],0) AS FirstName,
SplitName([NameAddressEtc],1) AS LastName,
SplitName([NameAddressEtc],2) AS Title,
Mid$([NameAddressEtc],InStrRev([NameAddressEtc],Chr(32))+1) AS zip
FROM tblTest;


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thanks everyone! Tom, I tried what you suggested but that will not work for
me. The one field has "groups" of information in it - example: one record
may have "John Smith Trustee FBO John Smith 123 Main Street
Anytown, US 12345" in that field. The SQL query breaks it up as "John",
"Smith", "Trustee". Whereas, I want the "groups" to stay together (example:
"John Smith Trustee", "FBO John Smith", "123 Main Street", "Anytown, US
12345".

I appreciate the help - why they set up the backend like this, I'll never
know.

Thanks again!

As "ti976" indicates, if the field is structured consistently it is possible.
I suspect that your task will be complicated by having variable word length.
However, with that said, here is a starting point that you might be able to
build upon. First, create a new module. Copy and paste the following function
into this new module:

Public Function SplitName(strFullName As String, _
intElement As Integer) As String
On Error Resume Next
Dim strResult() As String

strResult = Split([strFullName], Chr(32))

SplitName = strResult(intElement)

End Function


Here is a SQL statement for a query that breaks the first three values, and
the zip code, into separate fileds for a table named "tblTest" and a field
named "NameAddressEtc":

SELECT tblTest.NameAddressEtc,
SplitName([NameAddressEtc],0) AS FirstName,
SplitName([NameAddressEtc],1) AS LastName,
SplitName([NameAddressEtc],2) AS Title,
Mid$([NameAddressEtc],InStrRev([NameAddressEtc],Chr(32))+1) AS zip
FROM tblTest;


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I have a database with a linked table, so I am not able to change the table.
But one of the fields (Payee Address) contains all the information (separated
by spaces) that would normally be found in multiple fields (ex: "John Smith
Trustee FBO John Smith 123 Main Street Anytown, US 12345").
This data is tied to an account number.

I've tried using the trim function, to no avail. Is there a function that I
can create in a query so that the original table is not changed, but the data
I need (in real time) can be broken out into separate fields? I hope I'm
making sense. I've looked at this too long!

Thanks for your help!! You guys are the best!!

 

[Douglas J. Steele]

You indicated that the information is separated by spaces, but in your
example, each block of data appears to be separated by more than one space.

If, for example, there are always 3 spaces between each separate block of
data, use Tom's suggestion with:

strResult = Split([strFullName], Chr(32) & Chr(32) & Chr(32))

or

strResult = Split([strFullName], " ")



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks everyone! Tom, I tried what you suggested but that will not work
for
me. The one field has "groups" of information in it - example: one
record
may have "John Smith Trustee FBO John Smith 123 Main Street
Anytown, US 12345" in that field. The SQL query breaks it up as "John",
"Smith", "Trustee". Whereas, I want the "groups" to stay together
(example:
"John Smith Trustee", "FBO John Smith", "123 Main Street", "Anytown, US
12345".

I appreciate the help - why they set up the backend like this, I'll never
know.

Thanks again!

As "ti976" indicates, if the field is structured consistently it is
possible.
I suspect that your task will be complicated by having variable word
length.
However, with that said, here is a starting point that you might be able
to
build upon. First, create a new module. Copy and paste the following
function
into this new module:

Public Function SplitName(strFullName As String, _
intElement As Integer) As String
On Error Resume Next
Dim strResult() As String

strResult = Split([strFullName], Chr(32))

SplitName = strResult(intElement)

End Function


Here is a SQL statement for a query that breaks the first three values,
and
the zip code, into separate fileds for a table named "tblTest" and a
field
named "NameAddressEtc":

SELECT tblTest.NameAddressEtc,
SplitName([NameAddressEtc],0) AS FirstName,
SplitName([NameAddressEtc],1) AS LastName,
SplitName([NameAddressEtc],2) AS Title,
Mid$([NameAddressEtc],InStrRev([NameAddressEtc],Chr(32))+1) AS zip
FROM tblTest;


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I have a database with a linked table, so I am not able to change the
table.
But one of the fields (Payee Address) contains all the information
(separated
by spaces) that would normally be found in multiple fields (ex: "John
Smith
Trustee FBO John Smith 123 Main Street Anytown, US
12345").
This data is tied to an account number.

I've tried using the trim function, to no avail. Is there a function
that I
can create in a query so that the original table is not changed, but
the data
I need (in real time) can be broken out into separate fields? I hope
I'm
making sense. I've looked at this too long!

Thanks for your help!! You guys are the best!!

 

[Larry Linson]

If there are _always_ multiple spaces between the groups and nowhere else,
you might be able to use that fact with the Replace function to substitute a
different separator character so a Split function would separate by group
instead of by word.

Larry Linson
Microsoft Access MVP

Thanks everyone! Tom, I tried what you suggested but that will not work
for
me. The one field has "groups" of information in it - example: one
record
may have "John Smith Trustee FBO John Smith 123 Main Street
Anytown, US 12345" in that field. The SQL query breaks it up as "John",
"Smith", "Trustee". Whereas, I want the "groups" to stay together
(example:
"John Smith Trustee", "FBO John Smith", "123 Main Street", "Anytown, US
12345".

I appreciate the help - why they set up the backend like this, I'll never
know.

Thanks again!

As "ti976" indicates, if the field is structured consistently it is
possible.
I suspect that your task will be complicated by having variable word
length.
However, with that said, here is a starting point that you might be able
to
build upon. First, create a new module. Copy and paste the following
function
into this new module:

Public Function SplitName(strFullName As String, _
intElement As Integer) As String
On Error Resume Next
Dim strResult() As String

strResult = Split([strFullName], Chr(32))

SplitName = strResult(intElement)

End Function


Here is a SQL statement for a query that breaks the first three values,
and
the zip code, into separate fileds for a table named "tblTest" and a
field
named "NameAddressEtc":

SELECT tblTest.NameAddressEtc,
SplitName([NameAddressEtc],0) AS FirstName,
SplitName([NameAddressEtc],1) AS LastName,
SplitName([NameAddressEtc],2) AS Title,
Mid$([NameAddressEtc],InStrRev([NameAddressEtc],Chr(32))+1) AS zip
FROM tblTest;


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I have a database with a linked table, so I am not able to change the
table.
But one of the fields (Payee Address) contains all the information
(separated
by spaces) that would normally be found in multiple fields (ex: "John
Smith
Trustee FBO John Smith 123 Main Street Anytown, US
12345").
This data is tied to an account number.

I've tried using the trim function, to no avail. Is there a function
that I
can create in a query so that the original table is not changed, but
the data
I need (in real time) can be broken out into separate fields? I hope
I'm
making sense. I've looked at this too long!

Thanks for your help!! You guys are the best!!

 

[Guest]

Thanks again for the info. I was able to separate the data. I discovered
that there were 35 characters for each grouping within the field, so I used
the Left and Mid functions to create separate fields for each grouping, as
follows:

PayeeLine1: Left([PAYEE_ADDRESS_TX],35)
PayeeLine2: Mid([PAYEE_ADDRESS_TX],36,35)
PayeeLine3: Mid([PAYEE_ADDRESS_TX],71,35)
etc.

Thanks again!!

If there are _always_ multiple spaces between the groups and nowhere else,
you might be able to use that fact with the Replace function to substitute a
different separator character so a Split function would separate by group
instead of by word.

Larry Linson
Microsoft Access MVP

Thanks everyone! Tom, I tried what you suggested but that will not work
for
me. The one field has "groups" of information in it - example: one
record
may have "John Smith Trustee FBO John Smith 123 Main Street
Anytown, US 12345" in that field. The SQL query breaks it up as "John",
"Smith", "Trustee". Whereas, I want the "groups" to stay together
(example:
"John Smith Trustee", "FBO John Smith", "123 Main Street", "Anytown, US
12345".

I appreciate the help - why they set up the backend like this, I'll never
know.

Thanks again!

As "ti976" indicates, if the field is structured consistently it is
possible.
I suspect that your task will be complicated by having variable word
length.
However, with that said, here is a starting point that you might be able
to
build upon. First, create a new module. Copy and paste the following
function
into this new module:

Public Function SplitName(strFullName As String, _
intElement As Integer) As String
On Error Resume Next
Dim strResult() As String

strResult = Split([strFullName], Chr(32))

SplitName = strResult(intElement)

End Function


Here is a SQL statement for a query that breaks the first three values,
and
the zip code, into separate fileds for a table named "tblTest" and a
field
named "NameAddressEtc":

SELECT tblTest.NameAddressEtc,
SplitName([NameAddressEtc],0) AS FirstName,
SplitName([NameAddressEtc],1) AS LastName,
SplitName([NameAddressEtc],2) AS Title,
Mid$([NameAddressEtc],InStrRev([NameAddressEtc],Chr(32))+1) AS zip
FROM tblTest;


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

I have a database with a linked table, so I am not able to change the
table.
But one of the fields (Payee Address) contains all the information
(separated
by spaces) that would normally be found in multiple fields (ex: "John
Smith
Trustee FBO John Smith 123 Main Street Anytown, US
12345").
This data is tied to an account number.

I've tried using the trim function, to no avail. Is there a function
that I
can create in a query so that the original table is not changed, but
the data
I need (in real time) can be broken out into separate fields? I hope
I'm
making sense. I've looked at this too long!

Thanks for your help!! You guys are the best!!

 

[Pieter Wijnen]

AKA fixed length <g>

Pieter

Thanks again for the info. I was able to separate the data. I discovered
that there were 35 characters for each grouping within the field, so I
used
the Left and Mid functions to create separate fields for each grouping, as
follows:

PayeeLine1: Left([PAYEE_ADDRESS_TX],35)
PayeeLine2: Mid([PAYEE_ADDRESS_TX],36,35)
PayeeLine3: Mid([PAYEE_ADDRESS_TX],71,35)
etc.

Thanks again!!

If there are _always_ multiple spaces between the groups and nowhere
else,
you might be able to use that fact with the Replace function to
substitute a
different separator character so a Split function would separate by group
instead of by word.

Larry Linson
Microsoft Access MVP

Thanks everyone! Tom, I tried what you suggested but that will not
work
for
me. The one field has "groups" of information in it - example: one
record
may have "John Smith Trustee FBO John Smith 123 Main Street
Anytown, US 12345" in that field. The SQL query breaks it up as
"John",
"Smith", "Trustee". Whereas, I want the "groups" to stay together
(example:
"John Smith Trustee", "FBO John Smith", "123 Main Street", "Anytown, US
12345".

I appreciate the help - why they set up the backend like this, I'll
never
know.

Thanks again!

:

As "ti976" indicates, if the field is structured consistently it is
possible.
I suspect that your task will be complicated by having variable word
length.
However, with that said, here is a starting point that you might be
able
to
build upon. First, create a new module. Copy and paste the following
function
into this new module:

Public Function SplitName(strFullName As String, _
intElement As Integer) As String
On Error Resume Next
Dim strResult() As String

strResult = Split([strFullName], Chr(32))

SplitName = strResult(intElement)

End Function


Here is a SQL statement for a query that breaks the first three
values,
and
the zip code, into separate fileds for a table named "tblTest" and a
field
named "NameAddressEtc":

SELECT tblTest.NameAddressEtc,
SplitName([NameAddressEtc],0) AS FirstName,
SplitName([NameAddressEtc],1) AS LastName,
SplitName([NameAddressEtc],2) AS Title,
Mid$([NameAddressEtc],InStrRev([NameAddressEtc],Chr(32))+1) AS zip
FROM tblTest;


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

I have a database with a linked table, so I am not able to change
the
table.
But one of the fields (Payee Address) contains all the information
(separated
by spaces) that would normally be found in multiple fields (ex:
"John
Smith
Trustee FBO John Smith 123 Main Street Anytown, US
12345").
This data is tied to an account number.

I've tried using the trim function, to no avail. Is there a
function
that I
can create in a query so that the original table is not changed, but
the data
I need (in real time) can be broken out into separate fields? I
hope
I'm
making sense. I've looked at this too long!

Thanks for your help!! You guys are the best!!