split function on recordsets

[Guest]

Hi

i have a query that has one field which i would like to use the split
function. i open it using ADO. how do i add the split to the recordset.

example: column1 needs to be split. i use MySplitVar= split(column1) i then
have MySplitVar(0) and MySplitVar(1). how do add these two columns to my
recordset?

if you can't tell, i am new to this whole thing.

thanks,

sam

 

[Phillip Windell]

The minimal Syntax for the split function is:

SPLIT(<string>, <delimiter>)

Example:

Dim strActions as String
Dim aryWords as Array
strActions = "I tripped over my shoestring"

aryWords = Split(strAction, " ")

Will produce the following:
aryWords(0) = "I"
aryWords(1) = "tripped"
aryWords(2) = "over"
aryWords(3) = "my"
aryWords(4) = "shoestring"

That was off the top of my head, so it may be prone to minor errors, but it
should give you an idea of what I mean.

Now with that said, if you find youself having the "split" the value of a
field before it can be used,...then you probably shouldn't store the data in
the field the way you are doing it to begin with. The fields in the table
you be planned out and designed so that the data is already in a usable form
the way it comes out of the field. That isn't an "absolute" of course and
there are always exceptions to the rule, but that is the general idea to
keep in mind.

 

[Guest]

hi

what i am trying to do is get around a common issue in access. i am trying
to get crosstab with two total columns. since my first query already totals
two fields, i use a crosstab to get the first value of two fields. for
example: myTotalOfTwoFields:First([Field1] & " " & [Field2])
then in my report i would use a vba script that allows me to assign fields
to my boxes on open (i am sure you are familiar with that, if not see msdn:
creating a report based on a crosstab). the only problem i have is splitting
up this field.
i have some ideas on how to approach this, one of them involves creating
another recordset. my last idea that i am about to try is opening the query
as a recordset and then writing up an sql statement by looping through my
recorset fieldnames and then assigning that sql statement to my recordsource
of my report.

any thoughts on this would be greatly appreciated.

thanks and have a great weekend,

sam

The minimal Syntax for the split function is:

SPLIT(<string>, <delimiter>)

Example:

Dim strActions as String
Dim aryWords as Array
strActions = "I tripped over my shoestring"

aryWords = Split(strAction, " ")

Will produce the following:
aryWords(0) = "I"
aryWords(1) = "tripped"
aryWords(2) = "over"
aryWords(3) = "my"
aryWords(4) = "shoestring"

That was off the top of my head, so it may be prone to minor errors, but it
should give you an idea of what I mean.

Now with that said, if you find youself having the "split" the value of a
field before it can be used,...then you probably shouldn't store the data in
the field the way you are doing it to begin with. The fields in the table
you be planned out and designed so that the data is already in a usable form
the way it comes out of the field. That isn't an "absolute" of course and
there are always exceptions to the rule, but that is the general idea to
keep in mind.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

Hi

i have a query that has one field which i would like to use the split
function. i open it using ADO. how do i add the split to the recordset.

example: column1 needs to be split. i use MySplitVar= split(column1) i then
have MySplitVar(0) and MySplitVar(1). how do add these two columns to my
recordset?

if you can't tell, i am new to this whole thing.

thanks,

sam

 

[Phillip Windell]

what i am trying to do is get around a common issue in access. i am trying
to get crosstab with two total columns. since my first query already totals
two fields, i use a crosstab to get the first value of two fields. for
example: myTotalOfTwoFields:First([Field1] & " " & [Field2])
then in my report i would use a vba script that allows me to assign fields
to my boxes on open (i am sure you are familiar with that, if not see msdn:
creating a report based on a crosstab). the only problem i have is splitting
up this field.
i have some ideas on how to approach this, one of them involves creating
another recordset. my last idea that i am about to try is opening the query
as a recordset and then writing up an sql statement by looping through my
recorset fieldnames and then assigning that sql statement to my recordsource
of my report.

I don't think I can help with that. My knowledge of this stuff is very very
"generic",...I don't even remember what a cross-tab query is specifically in
the "world" of MS Access ( I probably know, but know it by another name).
Most of the stuff I have done with it is just using the MDB file with no
significant "interface" and using the MDB to run a website written in
Classic ASP, where the website becomes the interface.

 

[George Nicholson]

i am trying to get crosstab with two total columns.

http://support.microsoft.com/kb/209143/en-us
ACC2000: How to Create a Crosstab Query with Multiple Value Fields

You may be aware of this method, and are simply trying to find an
alternative approach. If so, please disregard.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

hi

what i am trying to do is get around a common issue in access. i am trying
to get crosstab with two total columns. since my first query already
totals
two fields, i use a crosstab to get the first value of two fields. for
example: myTotalOfTwoFields:First([Field1] & " " & [Field2])
then in my report i would use a vba script that allows me to assign fields
to my boxes on open (i am sure you are familiar with that, if not see
msdn:
creating a report based on a crosstab). the only problem i have is
splitting
up this field.
i have some ideas on how to approach this, one of them involves creating
another recordset. my last idea that i am about to try is opening the
query
as a recordset and then writing up an sql statement by looping through my
recorset fieldnames and then assigning that sql statement to my
recordsource
of my report.

any thoughts on this would be greatly appreciated.

thanks and have a great weekend,

sam

The minimal Syntax for the split function is:

SPLIT(<string>, <delimiter>)

Example:

Dim strActions as String
Dim aryWords as Array
strActions = "I tripped over my shoestring"

aryWords = Split(strAction, " ")

Will produce the following:
aryWords(0) = "I"
aryWords(1) = "tripped"
aryWords(2) = "over"
aryWords(3) = "my"
aryWords(4) = "shoestring"

That was off the top of my head, so it may be prone to minor errors, but
it
should give you an idea of what I mean.

Now with that said, if you find youself having the "split" the value of a
field before it can be used,...then you probably shouldn't store the data
in
the field the way you are doing it to begin with. The fields in the
table
you be planned out and designed so that the data is already in a usable
form
the way it comes out of the field. That isn't an "absolute" of course
and
there are always exceptions to the rule, but that is the general idea to
keep in mind.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

Hi

i have a query that has one field which i would like to use the split
function. i open it using ADO. how do i add the split to the recordset.

example: column1 needs to be split. i use MySplitVar= split(column1) i then
have MySplitVar(0) and MySplitVar(1). how do add these two columns to
my
recordset?

if you can't tell, i am new to this whole thing.

thanks,

sam