Text To Columns in Access


G

Guest

Does Access have a Text-To-Columns function like Excel?

I have a field in access that contains AAA-BBBB-CCC-DDDD or
AAAA-BBB-CCCC-DDD. I need to be able to split this data into 4 new fields
like Text-To-Columns in Excel. Please advise. THANKS!
 
Ad

Advertisements

G

Guest

I haven't seen this capability in Access (but that doesn't mean it isn't
there). Have you tried exporting the data to Excel, running the function
there, and reimporting the results to Access?

Randall Arnold
 
R

Rick B

You would build an update query and put the values into new fields using the
update query (assuming this is a one-time thing). You DO NOT want to store
all five fields. Create the new four fields, update them, then delete the
combined field.

Storing both would be redundant and would lead to problems. Users would
need to be careful to update all the fields. Also, it is much more
efficient to string them together in a query, report, or form if you need
them in that format rather than have Access go do disk reads.

If you need more details, post back.
 
G

Guest

I guess I'm looking for the actual string command in an Access Query that
allows me to do just that. You are right, I do not need to store all 5
columns. Just the new ones. I am importing Excel reports from our
supplier every day (45-75 line items) that has the field that needs to be
separated and I don't want to do a lot of labor in Excel before bringing the
reformatted data into my database.
 
G

Guest

I suppose I could but that just makes life more cumbersome unless I build a
macro (which I will if I have to). I was hoping there was a way that I could
import the supplier-provided spreadsheet (with the one column) into a table
and have a query expand the column to 4 columns. I have 80,000 records in
my database right now and am adding many records every day.
 
D

Douglas J. Steele

Split([MyBigField], "-")(0) will give you the first field,
Split([MyBigField], "-")(1) will give you the second field and so on.
 
Ad

Advertisements

M

Marshall Barton

Ernie said:
Does Access have a Text-To-Columns function like Excel?

I have a field in access that contains AAA-BBBB-CCC-DDDD or
AAAA-BBB-CCCC-DDD. I need to be able to split this data into 4 new fields
like Text-To-Columns in Excel. Please advise. THANKS!

A2K and later have the Split function.
 
G

Guest

Never used that function before! Thanks Doug and a "helpful post" point to
you. ;)

Randall Arnold

Douglas J. Steele said:
Split([MyBigField], "-")(0) will give you the first field,
Split([MyBigField], "-")(1) will give you the second field and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ernie Sersen said:
I guess I'm looking for the actual string command in an Access Query that
allows me to do just that. You are right, I do not need to store all 5
columns. Just the new ones. I am importing Excel reports from our
supplier every day (45-75 line items) that has the field that needs to be
separated and I don't want to do a lot of labor in Excel before bringing
the
reformatted data into my database.
 
G

Guest

Thanks, Doug! 'ppreciate the help! Ernie

Randall Arnold said:
Never used that function before! Thanks Doug and a "helpful post" point to
you. ;)

Randall Arnold

Douglas J. Steele said:
Split([MyBigField], "-")(0) will give you the first field,
Split([MyBigField], "-")(1) will give you the second field and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ernie Sersen said:
I guess I'm looking for the actual string command in an Access Query that
allows me to do just that. You are right, I do not need to store all 5
columns. Just the new ones. I am importing Excel reports from our
supplier every day (45-75 line items) that has the field that needs to be
separated and I don't want to do a lot of labor in Excel before bringing
the
reformatted data into my database.

:

You would build an update query and put the values into new fields using
the
update query (assuming this is a one-time thing). You DO NOT want to
store
all five fields. Create the new four fields, update them, then delete
the
combined field.

Storing both would be redundant and would lead to problems. Users would
need to be careful to update all the fields. Also, it is much more
efficient to string them together in a query, report, or form if you need
them in that format rather than have Access go do disk reads.

If you need more details, post back.



--
Rick B



Does Access have a Text-To-Columns function like Excel?

I have a field in access that contains AAA-BBBB-CCC-DDDD or
AAAA-BBB-CCCC-DDD. I need to be able to split this data into 4 new
fields
like Text-To-Columns in Excel. Please advise. THANKS!
 
G

Guest

Ernie, you replied to my post instead of Doug's-- just make sure you give him
credit by clicking "yes" that HIS post helped. Mine wound up being just so
much spam. ; )

Randall Arnold

Ernie Sersen said:
Thanks, Doug! 'ppreciate the help! Ernie

Randall Arnold said:
Never used that function before! Thanks Doug and a "helpful post" point to
you. ;)

Randall Arnold

Douglas J. Steele said:
Split([MyBigField], "-")(0) will give you the first field,
Split([MyBigField], "-")(1) will give you the second field and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I guess I'm looking for the actual string command in an Access Query that
allows me to do just that. You are right, I do not need to store all 5
columns. Just the new ones. I am importing Excel reports from our
supplier every day (45-75 line items) that has the field that needs to be
separated and I don't want to do a lot of labor in Excel before bringing
the
reformatted data into my database.

:

You would build an update query and put the values into new fields using
the
update query (assuming this is a one-time thing). You DO NOT want to
store
all five fields. Create the new four fields, update them, then delete
the
combined field.

Storing both would be redundant and would lead to problems. Users would
need to be careful to update all the fields. Also, it is much more
efficient to string them together in a query, report, or form if you need
them in that format rather than have Access go do disk reads.

If you need more details, post back.



--
Rick B



Does Access have a Text-To-Columns function like Excel?

I have a field in access that contains AAA-BBBB-CCC-DDDD or
AAAA-BBB-CCCC-DDD. I need to be able to split this data into 4 new
fields
like Text-To-Columns in Excel. Please advise. THANKS!
 
G

Guest

Doug,

I tried entering this command into a new query column as CR1: Split([CR],
"-")(0) and it gave me 'the expression you entered contains an invalid .
(dot), ! operator or invalid parenthesis'. If I remove the (0), it gives me
an 'undefined function split in expression'

Any thoughts?



Douglas J. Steele said:
Split([MyBigField], "-")(0) will give you the first field,
Split([MyBigField], "-")(1) will give you the second field and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ernie Sersen said:
I guess I'm looking for the actual string command in an Access Query that
allows me to do just that. You are right, I do not need to store all 5
columns. Just the new ones. I am importing Excel reports from our
supplier every day (45-75 line items) that has the field that needs to be
separated and I don't want to do a lot of labor in Excel before bringing
the
reformatted data into my database.
 
Ad

Advertisements

D

Douglas J. Steele

Gee: didn't work for me either.

Create some functions that you can call:

Function F1(TextIn As String) As String
F1 = Split(TextIn, "-")(0)
End Function


Function F2(TextIn As String) As String
F2 = Split(TextIn, "-")(1)
End Function

and so on, then use CR1: F1([CR]), CR2: F2([CR]) and so on.

Sorry about that!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ernie Sersen said:
Doug,

I tried entering this command into a new query column as CR1: Split([CR],
"-")(0) and it gave me 'the expression you entered contains an invalid .
(dot), ! operator or invalid parenthesis'. If I remove the (0), it gives
me
an 'undefined function split in expression'

Any thoughts?



Douglas J. Steele said:
Split([MyBigField], "-")(0) will give you the first field,
Split([MyBigField], "-")(1) will give you the second field and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ernie Sersen said:
I guess I'm looking for the actual string command in an Access Query
that
allows me to do just that. You are right, I do not need to store all 5
columns. Just the new ones. I am importing Excel reports from our
supplier every day (45-75 line items) that has the field that needs to
be
separated and I don't want to do a lot of labor in Excel before
bringing
the
reformatted data into my database.

:

You would build an update query and put the values into new fields
using
the
update query (assuming this is a one-time thing). You DO NOT want to
store
all five fields. Create the new four fields, update them, then delete
the
combined field.

Storing both would be redundant and would lead to problems. Users
would
need to be careful to update all the fields. Also, it is much more
efficient to string them together in a query, report, or form if you
need
them in that format rather than have Access go do disk reads.

If you need more details, post back.



--
Rick B



message
Does Access have a Text-To-Columns function like Excel?

I have a field in access that contains AAA-BBBB-CCC-DDDD or
AAAA-BBB-CCCC-DDD. I need to be able to split this data into 4 new
fields
like Text-To-Columns in Excel. Please advise. THANKS!
 
G

Guest

I'll give it a shot, Doug. Thanks!

Douglas J. Steele said:
Gee: didn't work for me either.

Create some functions that you can call:

Function F1(TextIn As String) As String
F1 = Split(TextIn, "-")(0)
End Function


Function F2(TextIn As String) As String
F2 = Split(TextIn, "-")(1)
End Function

and so on, then use CR1: F1([CR]), CR2: F2([CR]) and so on.

Sorry about that!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ernie Sersen said:
Doug,

I tried entering this command into a new query column as CR1: Split([CR],
"-")(0) and it gave me 'the expression you entered contains an invalid .
(dot), ! operator or invalid parenthesis'. If I remove the (0), it gives
me
an 'undefined function split in expression'

Any thoughts?



Douglas J. Steele said:
Split([MyBigField], "-")(0) will give you the first field,
Split([MyBigField], "-")(1) will give you the second field and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I guess I'm looking for the actual string command in an Access Query
that
allows me to do just that. You are right, I do not need to store all 5
columns. Just the new ones. I am importing Excel reports from our
supplier every day (45-75 line items) that has the field that needs to
be
separated and I don't want to do a lot of labor in Excel before
bringing
the
reformatted data into my database.

:

You would build an update query and put the values into new fields
using
the
update query (assuming this is a one-time thing). You DO NOT want to
store
all five fields. Create the new four fields, update them, then delete
the
combined field.

Storing both would be redundant and would lead to problems. Users
would
need to be careful to update all the fields. Also, it is much more
efficient to string them together in a query, report, or form if you
need
them in that format rather than have Access go do disk reads.

If you need more details, post back.



--
Rick B



message
Does Access have a Text-To-Columns function like Excel?

I have a field in access that contains AAA-BBBB-CCC-DDDD or
AAAA-BBB-CCCC-DDD. I need to be able to split this data into 4 new
fields
like Text-To-Columns in Excel. Please advise. THANKS!
 
G

Guest

IT WORKED!!!! Thanks so much, Doug!!!

Douglas J. Steele said:
Gee: didn't work for me either.

Create some functions that you can call:

Function F1(TextIn As String) As String
F1 = Split(TextIn, "-")(0)
End Function


Function F2(TextIn As String) As String
F2 = Split(TextIn, "-")(1)
End Function

and so on, then use CR1: F1([CR]), CR2: F2([CR]) and so on.

Sorry about that!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ernie Sersen said:
Doug,

I tried entering this command into a new query column as CR1: Split([CR],
"-")(0) and it gave me 'the expression you entered contains an invalid .
(dot), ! operator or invalid parenthesis'. If I remove the (0), it gives
me
an 'undefined function split in expression'

Any thoughts?



Douglas J. Steele said:
Split([MyBigField], "-")(0) will give you the first field,
Split([MyBigField], "-")(1) will give you the second field and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I guess I'm looking for the actual string command in an Access Query
that
allows me to do just that. You are right, I do not need to store all 5
columns. Just the new ones. I am importing Excel reports from our
supplier every day (45-75 line items) that has the field that needs to
be
separated and I don't want to do a lot of labor in Excel before
bringing
the
reformatted data into my database.

:

You would build an update query and put the values into new fields
using
the
update query (assuming this is a one-time thing). You DO NOT want to
store
all five fields. Create the new four fields, update them, then delete
the
combined field.

Storing both would be redundant and would lead to problems. Users
would
need to be careful to update all the fields. Also, it is much more
efficient to string them together in a query, report, or form if you
need
them in that format rather than have Access go do disk reads.

If you need more details, post back.



--
Rick B



message
Does Access have a Text-To-Columns function like Excel?

I have a field in access that contains AAA-BBBB-CCC-DDDD or
AAAA-BBB-CCCC-DDD. I need to be able to split this data into 4 new
fields
like Text-To-Columns in Excel. Please advise. THANKS!
 
G

Guest

Hello!

I found this post VERY helpful -- thank you very much! Only question I have
is this. This is what some of my data looks like where I need to use the
split function:

511100.001
511200
540000.003

and so on. As you notice...some of the data will NOT have numbers after a
period...and so I am getting an error in my code that says "run-time error 9.
Subscript Out of Range"

Any ideas of how to get around this? I would like the second field of the
split function to appear as null if there are no numbers after the period.

Thank you very much in advance!!

MN

Douglas J. Steele said:
Gee: didn't work for me either.

Create some functions that you can call:

Function F1(TextIn As String) As String
F1 = Split(TextIn, "-")(0)
End Function


Function F2(TextIn As String) As String
F2 = Split(TextIn, "-")(1)
End Function

and so on, then use CR1: F1([CR]), CR2: F2([CR]) and so on.

Sorry about that!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ernie Sersen said:
Doug,

I tried entering this command into a new query column as CR1: Split([CR],
"-")(0) and it gave me 'the expression you entered contains an invalid .
(dot), ! operator or invalid parenthesis'. If I remove the (0), it gives
me
an 'undefined function split in expression'

Any thoughts?



Douglas J. Steele said:
Split([MyBigField], "-")(0) will give you the first field,
Split([MyBigField], "-")(1) will give you the second field and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I guess I'm looking for the actual string command in an Access Query
that
allows me to do just that. You are right, I do not need to store all 5
columns. Just the new ones. I am importing Excel reports from our
supplier every day (45-75 line items) that has the field that needs to
be
separated and I don't want to do a lot of labor in Excel before
bringing
the
reformatted data into my database.

:

You would build an update query and put the values into new fields
using
the
update query (assuming this is a one-time thing). You DO NOT want to
store
all five fields. Create the new four fields, update them, then delete
the
combined field.

Storing both would be redundant and would lead to problems. Users
would
need to be careful to update all the fields. Also, it is much more
efficient to string them together in a query, report, or form if you
need
them in that format rather than have Access go do disk reads.

If you need more details, post back.



--
Rick B



message
Does Access have a Text-To-Columns function like Excel?

I have a field in access that contains AAA-BBBB-CCC-DDDD or
AAAA-BBB-CCCC-DDD. I need to be able to split this data into 4 new
fields
like Text-To-Columns in Excel. Please advise. THANKS!
 
J

John Spencer

Test the size of the array returned by the split function to make sure there
are enough elements to return a value

Function F2(TextIn As String) As String
Dim tStr() as Variant

tStr = Split(TextIn,".")

IF Ubound(tStr)<1 Then
F2 = ""
Else
F2 = tStr(1)
End If

End Function

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

MacNut2004 said:
Hello!

I found this post VERY helpful -- thank you very much! Only question I
have
is this. This is what some of my data looks like where I need to use the
split function:

511100.001
511200
540000.003

and so on. As you notice...some of the data will NOT have numbers after a
period...and so I am getting an error in my code that says "run-time error
9.
Subscript Out of Range"

Any ideas of how to get around this? I would like the second field of the
split function to appear as null if there are no numbers after the period.

Thank you very much in advance!!

MN

Douglas J. Steele said:
Gee: didn't work for me either.

Create some functions that you can call:

Function F1(TextIn As String) As String
F1 = Split(TextIn, "-")(0)
End Function


Function F2(TextIn As String) As String
F2 = Split(TextIn, "-")(1)
End Function

and so on, then use CR1: F1([CR]), CR2: F2([CR]) and so on.

Sorry about that!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ernie Sersen said:
Doug,

I tried entering this command into a new query column as CR1:
Split([CR],
"-")(0) and it gave me 'the expression you entered contains an invalid
.
(dot), ! operator or invalid parenthesis'. If I remove the (0), it
gives
me
an 'undefined function split in expression'

Any thoughts?



:

Split([MyBigField], "-")(0) will give you the first field,
Split([MyBigField], "-")(1) will give you the second field and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
I guess I'm looking for the actual string command in an Access Query
that
allows me to do just that. You are right, I do not need to store
all 5
columns. Just the new ones. I am importing Excel reports from
our
supplier every day (45-75 line items) that has the field that needs
to
be
separated and I don't want to do a lot of labor in Excel before
bringing
the
reformatted data into my database.

:

You would build an update query and put the values into new fields
using
the
update query (assuming this is a one-time thing). You DO NOT want
to
store
all five fields. Create the new four fields, update them, then
delete
the
combined field.

Storing both would be redundant and would lead to problems. Users
would
need to be careful to update all the fields. Also, it is much more
efficient to string them together in a query, report, or form if
you
need
them in that format rather than have Access go do disk reads.

If you need more details, post back.



--
Rick B



message
Does Access have a Text-To-Columns function like Excel?

I have a field in access that contains AAA-BBBB-CCC-DDDD or
AAAA-BBB-CCCC-DDD. I need to be able to split this data into 4
new
fields
like Text-To-Columns in Excel. Please advise. THANKS!
 
Ad

Advertisements

G

Guest

This works great for the second half if it is blank....but what if the entire
field is blank? (the upper portion as well as the second?)

Thank you!!

John Spencer said:
Test the size of the array returned by the split function to make sure there
are enough elements to return a value

Function F2(TextIn As String) As String
Dim tStr() as Variant

tStr = Split(TextIn,".")

IF Ubound(tStr)<1 Then
F2 = ""
Else
F2 = tStr(1)
End If

End Function

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

MacNut2004 said:
Hello!

I found this post VERY helpful -- thank you very much! Only question I
have
is this. This is what some of my data looks like where I need to use the
split function:

511100.001
511200
540000.003

and so on. As you notice...some of the data will NOT have numbers after a
period...and so I am getting an error in my code that says "run-time error
9.
Subscript Out of Range"

Any ideas of how to get around this? I would like the second field of the
split function to appear as null if there are no numbers after the period.

Thank you very much in advance!!

MN

Douglas J. Steele said:
Gee: didn't work for me either.

Create some functions that you can call:

Function F1(TextIn As String) As String
F1 = Split(TextIn, "-")(0)
End Function


Function F2(TextIn As String) As String
F2 = Split(TextIn, "-")(1)
End Function

and so on, then use CR1: F1([CR]), CR2: F2([CR]) and so on.

Sorry about that!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Doug,

I tried entering this command into a new query column as CR1:
Split([CR],
"-")(0) and it gave me 'the expression you entered contains an invalid
.
(dot), ! operator or invalid parenthesis'. If I remove the (0), it
gives
me
an 'undefined function split in expression'

Any thoughts?



:

Split([MyBigField], "-")(0) will give you the first field,
Split([MyBigField], "-")(1) will give you the second field and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
I guess I'm looking for the actual string command in an Access Query
that
allows me to do just that. You are right, I do not need to store
all 5
columns. Just the new ones. I am importing Excel reports from
our
supplier every day (45-75 line items) that has the field that needs
to
be
separated and I don't want to do a lot of labor in Excel before
bringing
the
reformatted data into my database.

:

You would build an update query and put the values into new fields
using
the
update query (assuming this is a one-time thing). You DO NOT want
to
store
all five fields. Create the new four fields, update them, then
delete
the
combined field.

Storing both would be redundant and would lead to problems. Users
would
need to be careful to update all the fields. Also, it is much more
efficient to string them together in a query, report, or form if
you
need
them in that format rather than have Access go do disk reads.

If you need more details, post back.



--
Rick B



message
Does Access have a Text-To-Columns function like Excel?

I have a field in access that contains AAA-BBBB-CCC-DDDD or
AAAA-BBB-CCCC-DDD. I need to be able to split this data into 4
new
fields
like Text-To-Columns in Excel. Please advise. THANKS!
 
J

John Spencer

If the field is Null then test for that by changing the function or using an
IIF statement in the query

Field: IIF([SomeField] is Null,Null, F2([SomeField]))

or in the function

Function F2(TextIn)
Dim tStr() as Variant

If Len(TextIN & "") = 0 Then
F2 = Null
Else
tStr = Split(TextIn,".")

IF Ubound(tStr)<1 Then
F2 = ""
Else
F2 = tStr(1)
End If
End If

End Function


Actually, here is a more generic function that I've written. You use it by
telling it the string, the delimiter, and the section number

getSection([SomeField],".",1)
getSection([SomeField],".",2)
getSection([SomeField],".",3)

Public Function getSection(strIn, _
Optional strDelimiter As String = ";", _
Optional intSectionNumber As Integer = 1)
'==============================================================
' Procedure : getSection
' Author : John Spencer
' Purpose : Return section of string
' Access 2000 and later
'==========================================

Dim strArray As Variant

If Len(strIn & vbNullString) = 0 Then
getSection = strIn
Else
strArray = Split(strIn, strDelimiter, -1, vbTextCompare)

If UBound(strArray) >= intSectionNumber - 1 Then
getSection = strArray(intSectionNumber - 1)
Else
getSection = Null
End If

End If

End Function


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

MacNut2004 said:
This works great for the second half if it is blank....but what if the
entire
field is blank? (the upper portion as well as the second?)

Thank you!!

John Spencer said:
Test the size of the array returned by the split function to make sure
there
are enough elements to return a value

Function F2(TextIn As String) As String
Dim tStr() as Variant

tStr = Split(TextIn,".")

IF Ubound(tStr)<1 Then
F2 = ""
Else
F2 = tStr(1)
End If

End Function

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

MacNut2004 said:
Hello!

I found this post VERY helpful -- thank you very much! Only question I
have
is this. This is what some of my data looks like where I need to use
the
split function:

511100.001
511200
540000.003

and so on. As you notice...some of the data will NOT have numbers
after a
period...and so I am getting an error in my code that says "run-time
error
9.
Subscript Out of Range"

Any ideas of how to get around this? I would like the second field of
the
split function to appear as null if there are no numbers after the
period.

Thank you very much in advance!!

MN

:

Gee: didn't work for me either.

Create some functions that you can call:

Function F1(TextIn As String) As String
F1 = Split(TextIn, "-")(0)
End Function


Function F2(TextIn As String) As String
F2 = Split(TextIn, "-")(1)
End Function

and so on, then use CR1: F1([CR]), CR2: F2([CR]) and so on.

Sorry about that!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Doug,

I tried entering this command into a new query column as CR1:
Split([CR],
"-")(0) and it gave me 'the expression you entered contains an
invalid
.
(dot), ! operator or invalid parenthesis'. If I remove the (0), it
gives
me
an 'undefined function split in expression'

Any thoughts?



:

Split([MyBigField], "-")(0) will give you the first field,
Split([MyBigField], "-")(1) will give you the second field and so
on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
I guess I'm looking for the actual string command in an Access
Query
that
allows me to do just that. You are right, I do not need to store
all 5
columns. Just the new ones. I am importing Excel reports from
our
supplier every day (45-75 line items) that has the field that
needs
to
be
separated and I don't want to do a lot of labor in Excel before
bringing
the
reformatted data into my database.

:

You would build an update query and put the values into new
fields
using
the
update query (assuming this is a one-time thing). You DO NOT
want
to
store
all five fields. Create the new four fields, update them, then
delete
the
combined field.

Storing both would be redundant and would lead to problems.
Users
would
need to be careful to update all the fields. Also, it is much
more
efficient to string them together in a query, report, or form if
you
need
them in that format rather than have Access go do disk reads.

If you need more details, post back.



--
Rick B



message
Does Access have a Text-To-Columns function like Excel?

I have a field in access that contains AAA-BBBB-CCC-DDDD or
AAAA-BBB-CCCC-DDD. I need to be able to split this data into
4
new
fields
like Text-To-Columns in Excel. Please advise. THANKS!
 
Ad

Advertisements

G

Guest

PERFECT!

Thank you so much for all your help! :)

MN

John Spencer said:
If the field is Null then test for that by changing the function or using an
IIF statement in the query

Field: IIF([SomeField] is Null,Null, F2([SomeField]))

or in the function

Function F2(TextIn)
Dim tStr() as Variant

If Len(TextIN & "") = 0 Then
F2 = Null
Else
tStr = Split(TextIn,".")

IF Ubound(tStr)<1 Then
F2 = ""
Else
F2 = tStr(1)
End If
End If

End Function


Actually, here is a more generic function that I've written. You use it by
telling it the string, the delimiter, and the section number

getSection([SomeField],".",1)
getSection([SomeField],".",2)
getSection([SomeField],".",3)

Public Function getSection(strIn, _
Optional strDelimiter As String = ";", _
Optional intSectionNumber As Integer = 1)
'==============================================================
' Procedure : getSection
' Author : John Spencer
' Purpose : Return section of string
' Access 2000 and later
'==========================================

Dim strArray As Variant

If Len(strIn & vbNullString) = 0 Then
getSection = strIn
Else
strArray = Split(strIn, strDelimiter, -1, vbTextCompare)

If UBound(strArray) >= intSectionNumber - 1 Then
getSection = strArray(intSectionNumber - 1)
Else
getSection = Null
End If

End If

End Function


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

MacNut2004 said:
This works great for the second half if it is blank....but what if the
entire
field is blank? (the upper portion as well as the second?)

Thank you!!

John Spencer said:
Test the size of the array returned by the split function to make sure
there
are enough elements to return a value

Function F2(TextIn As String) As String
Dim tStr() as Variant

tStr = Split(TextIn,".")

IF Ubound(tStr)<1 Then
F2 = ""
Else
F2 = tStr(1)
End If

End Function

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

Hello!

I found this post VERY helpful -- thank you very much! Only question I
have
is this. This is what some of my data looks like where I need to use
the
split function:

511100.001
511200
540000.003

and so on. As you notice...some of the data will NOT have numbers
after a
period...and so I am getting an error in my code that says "run-time
error
9.
Subscript Out of Range"

Any ideas of how to get around this? I would like the second field of
the
split function to appear as null if there are no numbers after the
period.

Thank you very much in advance!!

MN

:

Gee: didn't work for me either.

Create some functions that you can call:

Function F1(TextIn As String) As String
F1 = Split(TextIn, "-")(0)
End Function


Function F2(TextIn As String) As String
F2 = Split(TextIn, "-")(1)
End Function

and so on, then use CR1: F1([CR]), CR2: F2([CR]) and so on.

Sorry about that!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Doug,

I tried entering this command into a new query column as CR1:
Split([CR],
"-")(0) and it gave me 'the expression you entered contains an
invalid
.
(dot), ! operator or invalid parenthesis'. If I remove the (0), it
gives
me
an 'undefined function split in expression'

Any thoughts?



:

Split([MyBigField], "-")(0) will give you the first field,
Split([MyBigField], "-")(1) will give you the second field and so
on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
I guess I'm looking for the actual string command in an Access
Query
that
allows me to do just that. You are right, I do not need to store
all 5
columns. Just the new ones. I am importing Excel reports from
our
supplier every day (45-75 line items) that has the field that
needs
to
be
separated and I don't want to do a lot of labor in Excel before
bringing
the
reformatted data into my database.

:

You would build an update query and put the values into new
fields
using
the
update query (assuming this is a one-time thing). You DO NOT
want
to
store
all five fields. Create the new four fields, update them, then
delete
the
combined field.

Storing both would be redundant and would lead to problems.
Users
would
need to be careful to update all the fields. Also, it is much
more
efficient to string them together in a query, report, or form if
you
need
them in that format rather than have Access go do disk reads.

If you need more details, post back.



--
Rick B



message
Does Access have a Text-To-Columns function like Excel?

I have a field in access that contains AAA-BBBB-CCC-DDDD or
AAAA-BBB-CCCC-DDD. I need to be able to split this data into
4
new
fields
like Text-To-Columns in Excel. Please advise. THANKS!
 
Ad

Advertisements


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

Top