Splitting field

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

I have a address field that has multiple lines separated by CRLF. How can I
split each line and insert into another table at the same time? Is it
possible via query or do I need to do it in code?

Thanks

Regards
 
Hi

I have a address field that has multiple lines separated by CRLF. How can I
split each line and insert into another table at the same time? Is it
possible via query or do I need to do it in code?

Thanks

Regards
Click to expand...

Depends. How MANY multiple lines? A constant, or a variable number?
Are there multiple distinct attributes (say, city and postcode) on the
same line or are they on separate lines?

You can use VBA code using the Split() function (see the online help)
to extract each line as a separate value, or a somewhat clunkier query
solution with code like

Left([Address], InStr([Address], Chr(13) & Chr(10) - 1))

for the first line,
Mid([Address, InStr([Address], Chr(13) & Chr(10) + 2,
InStr(InStr([Address], Chr(13) & Chr(10) + 2), Chr(13) & Chr(10)) - 1)

for the second, and increasingly more complex expressions for the
rest.

John W. Vinson[MVP]
 
Slight error in the formula there John.

You have the -1 and +2 inside the InStr() function in a couple places,
but it should be outside that in the Left() and Mid() function.

Left([Address], InStr([Address], Chr(13) & Chr(10)) - 1)

Mid([Address, InStr([Address], Chr(13) & Chr(10)) + 2,
InStr(InStr([Address], Chr(13) & Chr(10)) + 2), Chr(13) & Chr(10)) -
1)

John said:
Hi

I have a address field that has multiple lines separated by CRLF. How can I
split each line and insert into another table at the same time? Is it
possible via query or do I need to do it in code?

Thanks

Regards
Click to expand...

Depends. How MANY multiple lines? A constant, or a variable number?
Are there multiple distinct attributes (say, city and postcode) on the
same line or are they on separate lines?

You can use VBA code using the Split() function (see the online help)
to extract each line as a separate value, or a somewhat clunkier query
solution with code like

Left([Address], InStr([Address], Chr(13) & Chr(10) - 1))

for the first line,
Mid([Address, InStr([Address], Chr(13) & Chr(10) + 2,
InStr(InStr([Address], Chr(13) & Chr(10) + 2), Chr(13) & Chr(10)) - 1)

for the second, and increasingly more complex expressions for the
rest.

John W. Vinson[MVP]
Click to expand...
 
Untested air-code

SELECT Split([FirstName], "&")(0) & " " & [LastName] As Person1,
IIf(UBound(Split([FirstName], "&") = 1, Split([FirstName], "&")(1), Null) +
" " + [LastName] As Person2
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Slightly better untested air-code:

SELECT Trim(Split([FirstName], "&")(0)) & " " & [LastName] As Person1,
IIf(UBound(Split([FirstName], "&") = 1, Trim(Split([FirstName], "&")(1)),
Null) +
" " + [LastName] As Person2
FROM MyTable


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas J. Steele said:
Untested air-code

SELECT Split([FirstName], "&")(0) & " " & [LastName] As Person1,
IIf(UBound(Split([FirstName], "&") = 1, Split([FirstName], "&")(1), Null)
+ " " + [LastName] As Person2
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Shannon said:
On a related subject...Both husband and wife's names are in the FirstName
field and their common last name is in the LastName field. For most
reports
this is good. I'd like to make a query, though displaying the husband's
first name, space " " last name as query field, then the wife's first
name
(if there is a wife), " " wife's last name as a query field, so that they
can
be separated for signatures of each. Is this possible? Right now, a
first
name example looks like this: "John L. & Jane M." The cooresponding last
name might look like this "Doe." The "&" sign is always part of the
first
name, if there are two names.
Click to expand...
Click to expand...
 
That's not what I wrote. It should be

SELECT Split([FirstName],"&")(0) & " " & [LastName]

That uses the Split function on the [FirstName] field to break the field
every ampersand:

Split([FirstName],"&")

Even if there are no ampersands in the field, there should still be a first
element of the returned array

Split([FirstName],"&")(0)

I concatenate that value with a space and the last name.

For the other name, I again use the Split function, but now I want to make
sure that there are 2 separate names found

IIf(UBound(Split([FirstName], "&") = 1,

if there are 2 names, take the 1st name (remember that arrays returned by
Split start at element 0)

Split([FirstName], "&")(1)

If there aren't 2 names, I return Null

Using + as the concatenation operator means that Split([FirstName], "&")(1)
+ " " + [LastName] will return a complete name, while Null + " " +
[LastName] returns Null.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Shannon said:
I have: FirstOwnerName:SELECT Split([FirstName],(0) "&") & " " &
[LastName]

What is the expression in this?

Douglas J. Steele said:
Untested air-code

SELECT Split([FirstName], "&")(0) & " " & [LastName] As Person1,
IIf(UBound(Split([FirstName], "&") = 1, Split([FirstName], "&")(1), Null)
+
" " + [LastName] As Person2
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Shannon said:
On a related subject...Both husband and wife's names are in the
FirstName
field and their common last name is in the LastName field. For most
reports
this is good. I'd like to make a query, though displaying the
husband's
first name, space " " last name as query field, then the wife's first
name
(if there is a wife), " " wife's last name as a query field, so that
they
can
be separated for signatures of each. Is this possible? Right now, a
first
name example looks like this: "John L. & Jane M." The cooresponding
last
name might look like this "Doe." The "&" sign is always part of the
first
name, if there are two names.

:

On 15 Jan 2007 19:28:59 -0800, "CompGeek78" <[email protected]>
wrote:

Slight error in the formula there John.

Thanks... off the top of my head and I didn't check it!

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Okay, I did label it untested air-code for a reason! Unfortunately, I only
have Access 97 available to be at the moment, so I can't debug.

You could write a couple of functions to return the first name and the
second name (or Null if there isn't a second name)

Function GetFirstName(InputField As Variant) As String
GetFirstName = Trim(Split(InputField, "&")(0))
End Function

Function GetSecondName(InputField As Variant) As Variant
Dim varNames As Variant

varNames = Split(InputField, "&")
If UBound(varNames) > 0 Then
GetSecondName = Trim(varNames(1))
Else
GetSecondName = Null
End If

End Function

Your query would then be

SELECT GetFirstName([FirstName]) & " " &LastName AS Person1,
GetSecondName([FirstName]) + " " + LastName As Person2
FROM SampleData;

(This one I've tested! <g>)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Shannon said:
Sorry for the play by play, but now, an error says "The syntax of the
subqurey in this expression is incorrect. Check the subquery syntax and
enclose the subquery in parentheses.

Douglas J. Steele said:
That's not what I wrote. It should be

SELECT Split([FirstName],"&")(0) & " " & [LastName]

That uses the Split function on the [FirstName] field to break the field
every ampersand:

Split([FirstName],"&")

Even if there are no ampersands in the field, there should still be a
first
element of the returned array

Split([FirstName],"&")(0)

I concatenate that value with a space and the last name.

For the other name, I again use the Split function, but now I want to
make
sure that there are 2 separate names found

IIf(UBound(Split([FirstName], "&") = 1,

if there are 2 names, take the 1st name (remember that arrays returned by
Split start at element 0)

Split([FirstName], "&")(1)

If there aren't 2 names, I return Null

Using + as the concatenation operator means that Split([FirstName],
"&")(1)
+ " " + [LastName] will return a complete name, while Null + " " +
[LastName] returns Null.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Shannon said:
I have: FirstOwnerName:SELECT Split([FirstName],(0) "&") & " " &
[LastName]

What is the expression in this?

:

Untested air-code

SELECT Split([FirstName], "&")(0) & " " & [LastName] As Person1,
IIf(UBound(Split([FirstName], "&") = 1, Split([FirstName], "&")(1),
Null)
+
" " + [LastName] As Person2
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


On a related subject...Both husband and wife's names are in the
FirstName
field and their common last name is in the LastName field. For most
reports
this is good. I'd like to make a query, though displaying the
husband's
first name, space " " last name as query field, then the wife's
first
name
(if there is a wife), " " wife's last name as a query field, so that
they
can
be separated for signatures of each. Is this possible? Right now, a
first
name example looks like this: "John L. & Jane M." The cooresponding
last
name might look like this "Doe." The "&" sign is always part of the
first
name, if there are two names.

:

On 15 Jan 2007 19:28:59 -0800, "CompGeek78" <[email protected]>
wrote:

Slight error in the formula there John.

Thanks... off the top of my head and I didn't check it!

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
freevbcode.com has a split function for you.. ****ing nooB

-Aaron


Okay, I did label it untested air-code for a reason! Unfortunately, I only
have Access 97 available to be at the moment, so I can't debug.

You could write a couple of functions to return the first name and the
second name (or Null if there isn't a second name)

Function GetFirstName(InputField As Variant) As String
GetFirstName = Trim(Split(InputField, "&")(0))
End Function

Function GetSecondName(InputField As Variant) As Variant
Dim varNames As Variant

varNames = Split(InputField, "&")
If UBound(varNames) > 0 Then
GetSecondName = Trim(varNames(1))
Else
GetSecondName = Null
End If

End Function

Your query would then be

SELECT GetFirstName([FirstName]) & " " &LastName AS Person1,
GetSecondName([FirstName]) + " " + LastName As Person2
FROM SampleData;

(This one I've tested! <g>)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Shannon said:
Sorry for the play by play, but now, an error says "The syntax of the
subqurey in this expression is incorrect. Check the subquery syntax and
enclose the subquery in parentheses.

Douglas J. Steele said:
That's not what I wrote. It should be

SELECT Split([FirstName],"&")(0) & " " & [LastName]

That uses the Split function on the [FirstName] field to break the field
every ampersand:

Split([FirstName],"&")

Even if there are no ampersands in the field, there should still be a
first
element of the returned array

Split([FirstName],"&")(0)

I concatenate that value with a space and the last name.

For the other name, I again use the Split function, but now I want to
make
sure that there are 2 separate names found

IIf(UBound(Split([FirstName], "&") = 1,

if there are 2 names, take the 1st name (remember that arrays returned by
Split start at element 0)

Split([FirstName], "&")(1)

If there aren't 2 names, I return Null

Using + as the concatenation operator means that Split([FirstName],
"&")(1)
+ " " + [LastName] will return a complete name, while Null + " " +
[LastName] returns Null.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have: FirstOwnerName:SELECT Split([FirstName],(0) "&") & " " &
[LastName]

What is the expression in this?

:

Untested air-code

SELECT Split([FirstName], "&")(0) & " " & [LastName] As Person1,
IIf(UBound(Split([FirstName], "&") = 1, Split([FirstName], "&")(1),
Null)
+
" " + [LastName] As Person2
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


On a related subject...Both husband and wife's names are in the
FirstName
field and their common last name is in the LastName field. For most
reports
this is good. I'd like to make a query, though displaying the
husband's
first name, space " " last name as query field, then the wife's
first
name
(if there is a wife), " " wife's last name as a query field, so that
they
can
be separated for signatures of each. Is this possible? Right now, a
first
name example looks like this: "John L. & Jane M." The cooresponding
last
name might look like this "Doe." The "&" sign is always part of the
first
name, if there are two names.

:

On 15 Jan 2007 19:28:59 -0800, "CompGeek78" <[email protected]>
wrote:

Slight error in the formula there John.

Thanks... off the top of my head and I didn't check it!

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
Realistically, you're not going to be able to accomplish your goal without
using functions.

Create a new module, copy the 12 lines from my post and pasted it into the
module you just created. Save the module as, say, mdlNames (it's essential
that modules not have the same name as routines within them)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Shannon said:
Sorry, functions are over my head. I don't even know where they go.

Douglas J. Steele said:
Okay, I did label it untested air-code for a reason! Unfortunately, I
only
have Access 97 available to be at the moment, so I can't debug.

You could write a couple of functions to return the first name and the
second name (or Null if there isn't a second name)

Function GetFirstName(InputField As Variant) As String
GetFirstName = Trim(Split(InputField, "&")(0))
End Function

Function GetSecondName(InputField As Variant) As Variant
Dim varNames As Variant

varNames = Split(InputField, "&")
If UBound(varNames) > 0 Then
GetSecondName = Trim(varNames(1))
Else
GetSecondName = Null
End If

End Function

Your query would then be

SELECT GetFirstName([FirstName]) & " " &LastName AS Person1,
GetSecondName([FirstName]) + " " + LastName As Person2
FROM SampleData;

(This one I've tested! <g>)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Shannon said:
Sorry for the play by play, but now, an error says "The syntax of the
subqurey in this expression is incorrect. Check the subquery syntax
and
enclose the subquery in parentheses.

:

That's not what I wrote. It should be

SELECT Split([FirstName],"&")(0) & " " & [LastName]

That uses the Split function on the [FirstName] field to break the
field
every ampersand:

Split([FirstName],"&")

Even if there are no ampersands in the field, there should still be a
first
element of the returned array

Split([FirstName],"&")(0)

I concatenate that value with a space and the last name.

For the other name, I again use the Split function, but now I want to
make
sure that there are 2 separate names found

IIf(UBound(Split([FirstName], "&") = 1,

if there are 2 names, take the 1st name (remember that arrays returned
by
Split start at element 0)

Split([FirstName], "&")(1)

If there aren't 2 names, I return Null

Using + as the concatenation operator means that Split([FirstName],
"&")(1)
+ " " + [LastName] will return a complete name, while Null + " " +
[LastName] returns Null.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have: FirstOwnerName:SELECT Split([FirstName],(0) "&") & " " &
[LastName]

What is the expression in this?

:

Untested air-code

SELECT Split([FirstName], "&")(0) & " " & [LastName] As Person1,
IIf(UBound(Split([FirstName], "&") = 1, Split([FirstName], "&")(1),
Null)
+
" " + [LastName] As Person2
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


On a related subject...Both husband and wife's names are in the
FirstName
field and their common last name is in the LastName field. For
most
reports
this is good. I'd like to make a query, though displaying the
husband's
first name, space " " last name as query field, then the wife's
first
name
(if there is a wife), " " wife's last name as a query field, so
that
they
can
be separated for signatures of each. Is this possible? Right
now, a
first
name example looks like this: "John L. & Jane M." The
cooresponding
last
name might look like this "Doe." The "&" sign is always part of
the
first
name, if there are two names.

:

On 15 Jan 2007 19:28:59 -0800, "CompGeek78"
<[email protected]>
wrote:

Slight error in the formula there John.

Thanks... off the top of my head and I didn't check it!

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
As far as I know, you can't use Split in a query directly. There is just no
way to get a specific element out of the array.



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

Douglas J. Steele said:
Slightly better untested air-code:

SELECT Trim(Split([FirstName], "&")(0)) & " " & [LastName] As Person1,
IIf(UBound(Split([FirstName], "&") = 1, Trim(Split([FirstName], "&")(1)),
Null) +
" " + [LastName] As Person2
FROM MyTable


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas J. Steele said:
Untested air-code

SELECT Split([FirstName], "&")(0) & " " & [LastName] As Person1,
IIf(UBound(Split([FirstName], "&") = 1, Split([FirstName], "&")(1), Null)
+ " " + [LastName] As Person2
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Shannon said:
On a related subject...Both husband and wife's names are in the
FirstName
field and their common last name is in the LastName field. For most
reports
this is good. I'd like to make a query, though displaying the husband's
first name, space " " last name as query field, then the wife's first
name
(if there is a wife), " " wife's last name as a query field, so that
they can
be separated for signatures of each. Is this possible? Right now, a
first
name example looks like this: "John L. & Jane M." The cooresponding
last
name might look like this "Doe." The "&" sign is always part of the
first
name, if there are two names.

:

On 15 Jan 2007 19:28:59 -0800, "CompGeek78" <[email protected]>
wrote:

Slight error in the formula there John.

Thanks... off the top of my head and I didn't check it!

John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Split data in field on character return 0
Splitting string 11
Split address field 3
VBA Multiple Line handling with Split() 1
Splitting field into separate excel cells. 3
Mobile phone texts split into incoming and outgoing messages 0
One Field splits to four fields 4
Word Removing unwanted "lines" within a MS Word 10 table 1

Back
Top