Split a field in different fields

[pburki]

Hello,

I have values like this is a cell:

45:45:64.65
132:45:32.1

I would like to be able to split these fields in 3 fields in a query. I
cannot use the left and right function as the number of characters is not
always identical. Is this possible to split text that is before, between and
after delimiter?

I need to do calculation on these values.

Thanks

Philippe

 

[pietlinden]

Hello,

I have values like this is a cell:

45:45:64.65
132:45:32.1

I would like to be able to split these fields in 3 fields in a query. I
cannot use the left and right function as the number of characters is not
always identical. Is this possible to split text that is before, between and
after delimiter?

I need to do calculation on these values.

Thanks

Philippe

 

[pietlinden]

Hello,

I have values like this is a cell:

45:45:64.65
132:45:32.1

I would like to be able to split these fields in 3 fields in a query. I
cannot use the left and right function as the number of characters is not
always identical. Is this possible to split text that is before, between and
after delimiter?

I need to do calculation on these values.

Thanks

Philippe

I would normalize my data first.

Public Sub SplitRepeatingNumbers()
Dim rs As DAO.Recordset
Dim intSubscript As Integer
Dim vNumbers As Variant

Set rs = DBEngine(0)(0).OpenRecordset("tbl1", dbOpenTable,
dbReadOnly)
Do Until rs.EOF
vNumbers = Split(rs.Fields("RepeatingNumbers"), ":")
For intSubscript = LBound(vNumbers) To UBound(vNumbers)

Debug.Print vNumbers(intSubscript)

Next intSubscript
Debug.Print "---------"
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

Instead of just writing the value of vNumbers(intSubscript) to the
debug window, I would write it to a table and then you can query it
any way you want.

 

[John Spencer]

You can do this in a query by using the Instr and InstrRev to find the
position of the colon.

Left(TheField, Instr(1,TheField,":")-1)

Mid(TheField,Instr(1,TheField,":")+1,
InstrRev(TheField,":")-Instr(1,TheField,":")-1)

Mid(TheField,InstrRev(TheField,":")+1)

Or you could write a custom VBA function to get the parts and use that (see below)

Field: getSection(TheField,":",1)

Field: getSection(TheField,":",2)

Field: getSection(TheField,":",3)

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

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

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County