Need a bit of help with the "instr" command...

[Guest]

Hi there,

I'm a bit stuck at the moment, so any help will be really handy!

I'm trying to split a column into several, and I know that there is a
character that is present to signify the end of each column (eg the data in
the column is "xxxxx<end>xx<end>xxxxxxx<end>x<end>xxxxxxxxxxx<end>". I know
that "instr" will find the first occurance of a given character, but how
would I get it to show me where the next one is, plus the ones after, as
there is no fixed position to start the "instr"?

The way i was going to do it for the first column was:

1:
Trim(Left([TmpData]![AllAddress],InStr(1,[TmpData]![AllAddress],"<end>")-1))

but I ran into trouble for the second column;

2: Mid([TmpData]![AllAddress],InStr(1,[TmpData]![AllAddress],"<end>")+1,100)

I put 100 characters in as I didn't know how to capture the length between
the first <end> and the next <end>

Does anyone have any idea how to solve this one? I'd really appreciate any
help here!

Thanks,

K

 

[John Spencer]

Are you doing this in a query? If so, this gets really complicated.
Especially if you have more than two columns.
I would write a custom user function.

If your are using Access 2000 or later the following may work for you.
Paste the function below into a module. Save the module (with some name
other than getSection.

In your query, you would need to call the function for each column

Field: getCol1: getSection([TmpData].[AllAddress],"<end>",1)
Field: GetCol2:getSection([TmpData].[AllAddress],"<end>",2)

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

 

[Guest]

Yeah, I was trying to be clever and do it in SQL as my knowledge of VBA is
pretty bad.

Thanks for your help, I'll give it a go!

Are you doing this in a query? If so, this gets really complicated.
Especially if you have more than two columns.
I would write a custom user function.

If your are using Access 2000 or later the following may work for you.
Paste the function below into a module. Save the module (with some name
other than getSection.

In your query, you would need to call the function for each column

Field: getCol1: getSection([TmpData].[AllAddress],"<end>",1)
Field: GetCol2:getSection([TmpData].[AllAddress],"<end>",2)

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

Hi there,

I'm a bit stuck at the moment, so any help will be really handy!

I'm trying to split a column into several, and I know that there is a
character that is present to signify the end of each column (eg the data
in
the column is "xxxxx<end>xx<end>xxxxxxx<end>x<end>xxxxxxxxxxx<end>". I
know
that "instr" will find the first occurance of a given character, but how
would I get it to show me where the next one is, plus the ones after, as
there is no fixed position to start the "instr"?

The way i was going to do it for the first column was:

1:
Trim(Left([TmpData]![AllAddress],InStr(1,[TmpData]![AllAddress],"<end>")-1))

but I ran into trouble for the second column;

2:
Mid([TmpData]![AllAddress],InStr(1,[TmpData]![AllAddress],"<end>")+1,100)

I put 100 characters in as I didn't know how to capture the length between
the first <end> and the next <end>

Does anyone have any idea how to solve this one? I'd really appreciate
any
help here!

Thanks,

K