split function on a recordset

Hi

i open a query as a recordset in VBA. i then need to use the split function to split up a few fields. how do i do this for the entire recordset.

for example:
MySplitVar = split(rst1("Field1"))
now i need
rst("Field2") = MySplitVar(0)
rst("Field3") = MySplitVar(1)

keep in mind i can't write to table. i need to somehow create a new recordset (maybe clone) and add columns to it. also, i don't know if this is relevant, but i will need to do this for up to 12 columns which means that i will have two per column; i.e. total columns=24

I see that nobody is posting an answer so i will add an explanation what i am doing.

i came up with a great idea. i need a crosstab that shows two values. so i created a a column that has both my columns in one column. value column shows the following:
myValue: = myFirstValue & " " & mySecondValue
and in the total i select First. so now i have a crosstab that shows two numbers with a space between them. now i have a report that will show this crosstab query. then i set each field's controlsource on the report to a particular field on the crosstab. my problem is that i first need to split it up and i am stuck there. i need to somehow add columns to the recordset.


thanks,

sam

Hi

Please have a look of .mdb in attachment, may be it can help you.....

Rahulgty

Thanks - still not sure

Hi

First, i greatly appreciate the example, i think it really explains the split function and serves as a real life example. i recommend all to try this example if you need the spit function.
(btw, there is a mistake in SplitByComa. strResult(intlElment) should be "intelement" typo. the only reason it worked is that the element component was not passed and it considered the intElment=0. if you correct the spelling (in the VBA) you also most correct the passing variable to 0 instead of 2 (in the query).

Second,my problem is still not solved. i will try to explain my problem.

query1
field1= department
field2=month grouping (i.e. group by month(mydate) & ", " & year(mydate))
field3=SumOfHours
field4=SumOfDollars

query2
pivot query1
Field1=Row
Field2=Column
Field3=Field3 & " " & Field4
this accomplishes a transpose on two fields the SumOfHours and SumOfDollars

the query works fine. now i have a VBA script which allows me to use a Crosstab on a report (i have a posting on this and i posted there similar code). what i need is to split Field3 after i run the query as a recordset.

what i am thinking of doing is creating a new recordset adding all fields of the first recordset and then adding more fields to it (basically its count(field2)*2)
and then adding my data after i split up query2.field3

what do you think? i am not so good at VBA, and this will take me a long time to do. but i was thinking maybe there is a quick and dirty way that i don't know about.

thanks again,

sam

Thanks for correction...

Yea u are correct, actually its typing mistake which happened during modifying the code for Post. Any way you are welcome.

Regarding your problem, it will be better if you will send me sample of your table and query, I will try to findout the way.

rahulgty

Rather than using split you should be able to use Left and Right functions in the SQL to split the data for your fields

Peter

Hi Bat17

How you will ascertain the integer value of text in Names to split in Left or right function?

rahulgty

Val(left([myfield],2))
Val(right([myfield],4))

Peter

oops think I was looking at the wrong field, try:-

Val(Left([MyField], InStr([MyField], " ")))
Val(Mid([MyField], InStr([MyField], " ")))

Peter

Re: Hi

rahulgty said:

Please have a look of .mdb in attachment, may be it can help you.....

Rahulgty

Click to expand...

Thank you for the example, it helped me a lot!