split function error

[MArk]

Hi all,
got small problem got one text field that produces 3 text numbers that can
vary in size. ie. 33,41,101
I want to use the split function to create three new fields.
My Split function is currently:

Function SplitPrice(Opening1 As String) As String
SplitOdds = Split(Opening1, ",")(0)
End Function

And in my query when i type:
Price1: Split([Prices])
So I get the first one fine(eg. 33) but how do i get the other 2 numbers
Price2: Split([Prices],2) doesnt work
Price3: Split([Prices],3) doesnt work

Any ideas?
Thanks

 

[giorgio rancati]

Hi MArk,
modify your function like this
----
Function SplitPrice(ByVal Opening1 As String, ByVal item As Integer) As
String
SplitPrice = Split(Opening1, ",")(item)
End Function
----

then insert the prices fields like these.
Price1: SplitPrice ([Prices],0)
Price2: SplitPrice ([Prices],1)
Price3: SplitPrice ([Prices],2)

bye

 

[Baz]

Here's another approach which doesn't create a redundant array each time you
call the function:

Function SplitPrice(ByRef Opening1 As String, ByVal Position As Integer) As
String
Dim intComma1 As Integer
Dim intComma2 As Integer

intComma1 = Instr([Prices],",")
intComma2 = Instr(intComma1 + 1,[Prices],",")

Select Case Position
Case 1
SplitPrice = Left(Opening1,intComma1-1)
Case 2
SplitPrice = Mid(Opening1,intComma1+1,intComma2-intComma1-1)
Case 3
SplitPrice = Mid(Opening1,intComma2+1)
End Select
End Function

 

[fredg]

Hi all,
got small problem got one text field that produces 3 text numbers that can
vary in size. ie. 33,41,101
I want to use the split function to create three new fields.
My Split function is currently:

Function SplitPrice(Opening1 As String) As String
SplitOdds = Split(Opening1, ",")(0)
End Function

And in my query when i type:
Price1: Split([Prices])
So I get the first one fine(eg. 33) but how do i get the other 2 numbers
Price2: Split([Prices],2) doesnt work
Price3: Split([Prices],3) doesnt work

Any ideas?
Thanks

Copy and Paste the following into a Module:

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, ",", -1)
ParseText = var(X)

End Function
===========

Then in your Query:

Price1 = ParseText([Pricesl],0)
Price2 = ParseText([Prices],1)
Price3 = ParseText([Prices],2)

Note that the Split function is Zero based, so the first column is 0.

 

[MArk]

Thanks all
fredg yours work how i wanted it.ta

Hi all,
got small problem got one text field that produces 3 text numbers that can
vary in size. ie. 33,41,101
I want to use the split function to create three new fields.
My Split function is currently:

Function SplitPrice(Opening1 As String) As String
SplitOdds = Split(Opening1, ",")(0)
End Function

And in my query when i type:
Price1: Split([Prices])
So I get the first one fine(eg. 33) but how do i get the other 2 numbers
Price2: Split([Prices],2) doesnt work
Price3: Split([Prices],3) doesnt work

Any ideas?
Thanks

Copy and Paste the following into a Module:

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, ",", -1)
ParseText = var(X)

End Function
===========

Then in your Query:

Price1 = ParseText([Pricesl],0)
Price2 = ParseText([Prices],1)
Price3 = ParseText([Prices],2)

Note that the Split function is Zero based, so the first column is 0.