Split semicolon separated field into 12 new fields

[tomaccess]

I'm new to this group and have some experience in Access, but only in writing simple query statements. My problem is that I have a memo data field with 12 values separated by a ";". The data field is called MEB-lyr.

5782.7;5782.7;11408.92;7586.75;9598.69;10659.02;2939.58;2939.58;7474.24;11353.24;13616.41;9420.71

I would like to write some type of query statement or VBA code (for newbies) that would separate these 12 combined values into 12 new data fields (with no ";"'s), as follows:

5782.7;5782.7;11408.92;7586.75;9598.69;10659.02;2939.58;2939.58;7474.24;11353.24;13616.41;9420.71

With field names of MEB1-lyr, MEB2-lyr, MEB3-lyr, and so on to MEB12-lyr.

Can anyone help me out? I've looked through the FAQ but I can't seem to put it together!

Thanks!

 

[RoyVidar]

I think you'd need to loop the recordset to be able to do that.

[tt]dim rs as dao.recordset
dim lngCounter as long
dim arr() as string
set rs=currentdb.openrecordset("nameoftable",dbopentable)
do while not rs.eof
arr=split(rs.fields("MEB-lyr").value,";")
rs.edit
for lngcounter=0 to ubound(arr)
rs.fields("[MEB" & cstr(lngcounter+1) & "-lyr]").value = csng(arr(lngcounter))
next lngcounter
rs.update
rs.movenext
loop
rs.close
set rs=nothing[/tt]

- typed - not tested, would need a reference to Microsoft DAO 3.# Object library (in VBE - Tools | References), requires 2000+ version (split function)

Roy-Vidar

 

[PHV]

In a standard code module create the following function:

Public Function mySplit(str, delim, n)
On Error Resume Next
mySplit = Split(str, delim)(n - 1)
End Function

And now your query:
SELECT mySplit([MEB-lyr],';',1) AS MEB1-lyr,
..., mySplit([MEB-lyr],';',12) AS MEB12-lyr
FROM ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tomaccess]

Thanks for your input, PH. This is what I tried so far...
I added a module named mysplit with the following code (the option compare database was already there when I opened the module):

Option Compare Database
Public Function mysplit(str, delim, n)
On Error Resume Next
mysplit = Split(str, delim)(n - 1)
End Function

Then I opened a new query and added the table Pub_GLA.

In the first box I did the following:

Field: meb-lyr (should this be a new field? - like Meb1-lyr?
Table: Pub_GLA
Sort: N/A
Show: checked
Criteria: Is this where I paste your select statement? When I do this I get syntax errors. Also, after From in your statement should it be "From [pub_gla]?

Thanks for any help you can provide - sorry for the rookie questions!

Tom

 

[AppStaff]

You could do this potentially without code using excel or access import functions. If this is a one shot or infrequently done thing I would do it without code.

general steps w/out code:

Save the data as a text file, import in as ";" delimited. this should separate the semicolons into separate columns for you. Of course if you have leading and trailing data without semi-colons it won't bring that in separate it will all just be lumped into the first and last of the semicolon fields. You can easily match them back up later though.

 

[tomaccess]

Appstaff,

Thanks, but it will be a monthly process so I'm hoping to automate the split.

 

[PHV]

First the module and the function shouldn't have the same name (mysplit), so rename the module.
In the query grid:
Field: MEB1-lyr: mySplit([MEB-lyr],';',1)
Table: Pub_GLA
Sort:
Show: checked
Criteria:
...
Field: MEB12-lyr: mySplit([MEB-lyr],';',12)
Table: Pub_GLA
Sort:
Show: checked
Criteria:

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tomaccess]

Hi PH,

Almost there (I think!) - I renamed the mysplit module splitfield but kept the contents the same otherwise. After I put in your code in the query grid when I run the query I get the following message:

"Undefined function 'mysplit' in expression"

Thanks again for all your help so far - any ideas on what I need to do from here? I think once I get one query expression to run the rest will be easy.

Thanks,

Tom

 

[Dryseals]

Roy,
Thanks for the split function, and to think I've been doing it by searching a mid function for years.

 

[tomaccess]

Dryseals,

Do you know why I would be getting the error message "Undefined function 'mysplit' in expression"?

 

[tomaccess]

Thanks for all (especially PH) who have helped me thus far on splitting 1 field with 12 numbers separated by semicolons into 12 separate numeric fields. I'll provide an update so others might be able to help me figure this out!

Data field is called MEB-lyr, data looks like:
5782.7;5782.7;11408.92;7586.75;9598.69;10659.02;2939.58;2939.58;7474.24;11353.24;13616.41;9420.71

Per PH's instructions I put the following function in a class module called splitfield:

Option Compare Database
Public Function mySplit(str, delim, n)
On Error Resume Next
mySplit = Split(str, delim)(n - 1)
End Function

Then I opened a new query and in the Query gridbox I have:
Field: MEB1-lyr: mySplit([MEB-lyr],';',1)
Table: PUB_gla
Sort:
Show: Checked

When I run the query I get the following message:
Syntax error (comma) in query expression 'PUB_gla.[MEB1-lyr: mySplit([MEB-lyr],';',1)].

Can anyone provide me with any troubleshooting ideas? I feel like I'm so close!

Thanks!

 

[PHV]

1) The module must be a standard code module (not a form nor a report nor a class module)
2) The Table cell must be empty for the 12 expressions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244