Devide a colum into multiple columns in an access query

[Jeronimo]

Hello,

I have a table in access that is linked via an ODBC connection and it
has a field wich contains 12 values.

This is how the field looks like:

AAA;BBB;CCCCC;DD;EEEEEEE;FFF;GGGGG;H;I;JJJ;KK;LLLLL

This is like I said before 1 field and I would like to devide this into
12 different columns.

I tried to do this with the left, right and mid functions but for 3
values in 1 field it could work, but I have 12 values, so i need 12
columns!

Can anyone help me with this, how should I do this?

Thanks,

Jeronimo

 

[John Spencer]

Are the lengths of each field the same? Or is field delimited by the
semi-colon and the values in the fields are of varying lengths? What
version of Access are you using? If 2000 or later you have a VBA function
(Split) which you could use in a custom function to return each column.

 

[Jeronimo]

The length of each field is not the same, it are all varying lengths
and it is delimited by the semi-colon.

I am familiar with access and query's but not that much wich VB in
access.

How do I make that kind of function in access and how do I get it to
work on my query so I have 12 different columns.

Thanks for your king answer.

Best Regards,

Jeronimo

 

[Jeronimo]

The length of each field is not the same, it are all varying lengths
and it is delimited by the semi-colon.

I am familiar with access and query's but not that much wich VB in
access.

How do I make that kind of function in access and how do I get it to
work on my query so I have 12 different columns.

Thanks for your king answer.

Best Regards,

Jeronimo

 

[John Spencer]

Here is some code you can put into a VBA module and call from your query.
You should be able to call this in a query just like any other functions.

Field: FieldFive: getSection([YourExistingField],";",5)

That should return EEEEEEE from your example text.

The code is not fully tested and does not have an error routine built in,
but it should work

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

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

 

[Jeronimo]

Hi John,

Thanks for your help!

I Tried it in a query and it works perfect !!

Altough I do not really understand all of the things that are in your
function I am very happy that I can deal now with such problems.

Should you have a little bit more time I should like to receive some
more information about your function so I would understand it better.

Perhaps if I understand it well I can make function on my one.

Only If you have some time explain me further your function.

Many, many thanks and best regards.

Jeronimo

 

[John Spencer]

Here is my code with some more comments in it. You can also highlight any
of the functions (DIM, Split, Ubound) and press the F1 key to get further
explanations of what they do, how they work from MS Access Help.

Public Function getSection(strIn, _
Optional strDelimiter As String = ";", _
Optional intSectionNumber As Integer = 1)
'===================================================
' Procedure : getSection
' Author : John Spencer
' Purpose : Return section of string
' StrIn - variant type - can be text, date, number, or blank (null)
' strDelimiter - a string that is used to split strIn into sections (if not
specified
' then use ;
' intSectionNumber - a number telling which section to return (if not
specified
' then use 1
' Optional (keyword) meaning no value is required for the argument when
' calling the function
'===================================================

Dim strArray As Variant

If Len(strIn & vbNullString) = 0 Then
' Check if strIn is Null or zero-characters in length. If so, return what
came in
getSection = strIn 'return the value
Else
'Use the VBA split function to break strIn into parts
'and store the parts in an array in memory
strArray = Split(strIn, strDelimiter, -1, vbTextCompare)

'Check to make sure that there are at least as many sections in the
'array as the section number we want. Since arrays (by default are
numbered
' starting with zero adjust the section number we want by -1
If UBound(strArray) >= intSectionNumber - 1 Then
getSection = strArray(intSectionNumber - 1) 'return the value

Else 'No section with the number intSectionNumber
getSection = Null 'return the value
End If

End If

End Function

 

[Jeronimo]

Thanks,

Now I Fully understand wath all code means.

Best Regards,

Jeronimo