Help with Split function

  • Thread starter Thread starter palto1
  • Start date Start date
P

palto1

Hello, I have a field [InjuryType] that contains a string
of values (i.e. 2;5;7;3, etc) separated by ";". I need to
split this field into separate fields so that there is a
new record for each value.

Someone on another newsgroups suggested the following code:

Function ParseandImportInjuryTypes()
Dim ParseString() As String
Dim SourceString As String

SourceString = ExcelLegalFactorsCapEarly.InjuryType
ParseString = Split(SourceString, ";", vbTextCompare)

MsgBox ParseString(0), "1st element of the array"

However, when I compile and run the code, I get an error
on the line the following line: SourceString =
ExcelLegalFactorsCapEarly.InjuryType. The error states
that an Object is required.

What am I doing wrong?

Thank you so much for your help.
 
Assuming your code is running in the code behind the form
ExcelLegalFactorsCapEarly, change that line to:

SourceString = Me!InjuryType
 
Thank you Doug, I actually have the code set in a Module
that gets launched when a button is pressed. In the line:

"SourceString = ExcelLegalFactorsCapEarly.InjuryType"

ExcelLegalFactorsCapEarly is the table that stores the
data imported from Excel. I also have a DoCmd.OpenQuery
line of code that follows that takes the InjuryType
values from that table and appends them onto another
table (hope that makes sense.) Basically, I'm trying
to "normalize" the data by using the Split fucntion to
parse out a field.

For some reason it can't find the data in this line of
code: SourceString = ExcelLegalFactorsCapEarly.InjuryType

What am I doing wrong?

Thanks again for your response.

Grace

-----Original Message-----
Assuming your code is running in the code behind the form
ExcelLegalFactorsCapEarly, change that line to:

SourceString = Me!InjuryType


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Hello, I have a field [InjuryType] that contains a string
of values (i.e. 2;5;7;3, etc) separated by ";". I need to
split this field into separate fields so that there is a
new record for each value.

Someone on another newsgroups suggested the following code:

Function ParseandImportInjuryTypes()
Dim ParseString() As String
Dim SourceString As String

SourceString = ExcelLegalFactorsCapEarly.InjuryType
ParseString = Split(SourceString, ";", vbTextCompare)

MsgBox ParseString(0), "1st element of the array"

However, when I compile and run the code, I get an error
on the line the following line: SourceString =
ExcelLegalFactorsCapEarly.InjuryType. The error states
that an Object is required.

What am I doing wrong?

Thank you so much for your help.
Click to expand...


.
Click to expand...
 
You can't refer to tables like that in VBA.

Did you try using Me!, as I suggested?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Thank you Doug, I actually have the code set in a Module
that gets launched when a button is pressed. In the line:

"SourceString = ExcelLegalFactorsCapEarly.InjuryType"

ExcelLegalFactorsCapEarly is the table that stores the
data imported from Excel. I also have a DoCmd.OpenQuery
line of code that follows that takes the InjuryType
values from that table and appends them onto another
table (hope that makes sense.) Basically, I'm trying
to "normalize" the data by using the Split fucntion to
parse out a field.

For some reason it can't find the data in this line of
code: SourceString = ExcelLegalFactorsCapEarly.InjuryType

What am I doing wrong?

Thanks again for your response.

Grace

-----Original Message-----
Assuming your code is running in the code behind the form
ExcelLegalFactorsCapEarly, change that line to:

SourceString = Me!InjuryType


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Hello, I have a field [InjuryType] that contains a string
of values (i.e. 2;5;7;3, etc) separated by ";". I need to
split this field into separate fields so that there is a
new record for each value.

Someone on another newsgroups suggested the following code:

Function ParseandImportInjuryTypes()
Dim ParseString() As String
Dim SourceString As String

SourceString = ExcelLegalFactorsCapEarly.InjuryType
ParseString = Split(SourceString, ";", vbTextCompare)

MsgBox ParseString(0), "1st element of the array"

However, when I compile and run the code, I get an error
on the line the following line: SourceString =
ExcelLegalFactorsCapEarly.InjuryType. The error states
that an Object is required.

What am I doing wrong?

Thank you so much for your help.
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks again. Unfortunately, it keeps telling me that it
is an invalid use of Me! How can I reference the "Me!" to
the ExcelLegalFactorsCapEarly table?

Thanks again, and sorry I am fairly new to the world of
VBA!

-----Original Message-----
You can't refer to tables like that in VBA.

Did you try using Me!, as I suggested?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Thank you Doug, I actually have the code set in a Module
that gets launched when a button is pressed. In the line:

"SourceString = ExcelLegalFactorsCapEarly.InjuryType"

ExcelLegalFactorsCapEarly is the table that stores the
data imported from Excel. I also have a DoCmd.OpenQuery
line of code that follows that takes the InjuryType
values from that table and appends them onto another
table (hope that makes sense.) Basically, I'm trying
to "normalize" the data by using the Split fucntion to
parse out a field.

For some reason it can't find the data in this line of
code: SourceString = ExcelLegalFactorsCapEarly.InjuryType

What am I doing wrong?

Thanks again for your response.

Grace

-----Original Message-----
Assuming your code is running in the code behind the form
ExcelLegalFactorsCapEarly, change that line to:

SourceString = Me!InjuryType


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Hello, I have a field [InjuryType] that contains a string
of values (i.e. 2;5;7;3, etc) separated by ";". I need to
split this field into separate fields so that there
Click to expand...
is
a
new record for each value.

Someone on another newsgroups suggested the following code:

Function ParseandImportInjuryTypes()
Dim ParseString() As String
Dim SourceString As String

SourceString = ExcelLegalFactorsCapEarly.InjuryType
ParseString = Split(SourceString, ";", vbTextCompare)

MsgBox ParseString(0), "1st element of the array"

However, when I compile and run the code, I get an error
on the line the following line: SourceString =
ExcelLegalFactorsCapEarly.InjuryType. The error states
that an Object is required.

What am I doing wrong?

Thank you so much for your help.


.
Click to expand...
Click to expand...


.
Click to expand...
 
Let's step back a moment.

You've got a form. What table/query is that form bound to?

If it's the table ExcelLegalFactorsCapEarly, add a text box bound to
InjuryType (it can be hidden if need be)

If it's a query based on ExcelLegalFactorsCapEarly, then ensure that the
InjuryType field is included in the query (and follow the table
instructions).

If it's not a table or query including ExcelLegalFactorsCapEarly (or if the
form is unbound), then try using DLookup rather than what you've got.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


palto1 said:
Thanks again. Unfortunately, it keeps telling me that it
is an invalid use of Me! How can I reference the "Me!" to
the ExcelLegalFactorsCapEarly table?

Thanks again, and sorry I am fairly new to the world of
VBA!

-----Original Message-----
You can't refer to tables like that in VBA.

Did you try using Me!, as I suggested?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Thank you Doug, I actually have the code set in a Module
that gets launched when a button is pressed. In the line:

"SourceString = ExcelLegalFactorsCapEarly.InjuryType"

ExcelLegalFactorsCapEarly is the table that stores the
data imported from Excel. I also have a DoCmd.OpenQuery
line of code that follows that takes the InjuryType
values from that table and appends them onto another
table (hope that makes sense.) Basically, I'm trying
to "normalize" the data by using the Split fucntion to
parse out a field.

For some reason it can't find the data in this line of
code: SourceString = ExcelLegalFactorsCapEarly.InjuryType

What am I doing wrong?

Thanks again for your response.

Grace


-----Original Message-----
Assuming your code is running in the code behind the form
ExcelLegalFactorsCapEarly, change that line to:

SourceString = Me!InjuryType


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



message
Hello, I have a field [InjuryType] that contains a
string
of values (i.e. 2;5;7;3, etc) separated by ";". I
need to
split this field into separate fields so that there is
a
new record for each value.

Someone on another newsgroups suggested the following
code:

Function ParseandImportInjuryTypes()
Dim ParseString() As String
Dim SourceString As String

SourceString = ExcelLegalFactorsCapEarly.InjuryType
ParseString = Split(SourceString, ";", vbTextCompare)

MsgBox ParseString(0), "1st element of the array"

However, when I compile and run the code, I get an
error
on the line the following line: SourceString =
ExcelLegalFactorsCapEarly.InjuryType. The error states
that an Object is required.

What am I doing wrong?

Thank you so much for your help.


.
Click to expand...


.
Click to expand...
Click to expand...
 
Ok, Douglas, I really appreciate your patience with me.
Here is my code so far:

Function ParseInjuryTypes()

Dim ParseString As String
Dim SourceString As String

SourceString =
DLookup "[InjuryType]", "ExcelLegalFactorsCapEarly")
MsgBox SourceString

ParseString = Split(SourceString, ";", vbTextCompare)
MsgBox ParseString(0), "1st element of the array"

I am able to now display the values in the SourceString
using DLookup. However, when I compile the code I am
getting the error: "Expected Array: On the line:

MsgBox ParseString(0), "1st element of the array"

Why can't it create the array? This has been very
frustrating for me as there is not too much out there on
the Split function.

Thank you once again!
-----Original Message-----
Let's step back a moment.

You've got a form. What table/query is that form bound to?

If it's the table ExcelLegalFactorsCapEarly, add a text box bound to
InjuryType (it can be hidden if need be)

If it's a query based on ExcelLegalFactorsCapEarly, then ensure that the
InjuryType field is included in the query (and follow the table
instructions).

If it's not a table or query including
Click to expand...
ExcelLegalFactorsCapEarly (or if the
form is unbound), then try using DLookup rather than what you've got.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Thanks again. Unfortunately, it keeps telling me that it
is an invalid use of Me! How can I reference the "Me!" to
the ExcelLegalFactorsCapEarly table?

Thanks again, and sorry I am fairly new to the world of
VBA!

-----Original Message-----
You can't refer to tables like that in VBA.

Did you try using Me!, as I suggested?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Thank you Doug, I actually have the code set in a Module
that gets launched when a button is pressed. In the line:

"SourceString = ExcelLegalFactorsCapEarly.InjuryType"

ExcelLegalFactorsCapEarly is the table that stores the
data imported from Excel. I also have a DoCmd.OpenQuery
line of code that follows that takes the InjuryType
values from that table and appends them onto another
table (hope that makes sense.) Basically, I'm trying
to "normalize" the data by using the Split fucntion to
parse out a field.

For some reason it can't find the data in this line of
code: SourceString = ExcelLegalFactorsCapEarly.InjuryType

What am I doing wrong?

Thanks again for your response.

Grace


-----Original Message-----
Assuming your code is running in the code behind the form
ExcelLegalFactorsCapEarly, change that line to:

SourceString = Me!InjuryType


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



message
Hello, I have a field [InjuryType] that contains a
string
of values (i.e. 2;5;7;3, etc) separated by ";". I
need to
split this field into separate fields so that
Click to expand...
there
is
a
new record for each value.

Someone on another newsgroups suggested the following
code:

Function ParseandImportInjuryTypes()
Dim ParseString() As String
Dim SourceString As String

SourceString = ExcelLegalFactorsCapEarly.InjuryType
ParseString = Split(SourceString, ";", vbTextCompare)

MsgBox ParseString(0), "1st element of the array"

However, when I compile and run the code, I get an
error
on the line the following line: SourceString =
ExcelLegalFactorsCapEarly.InjuryType. The error states
that an Object is required.

What am I doing wrong?

Thank you so much for your help.


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Since Split returns an array, you need to declare ParseString as an array,
or as a variant:

Dim ParseString() As String

or

Dim ParseString As Variant
 
Back
Top