Split Function Error Handling

[nguyenak]

Hi,



I link an csv file to Microsoft Sccess and wanted to create a module to do the following.

1. Extract the first url
2. Extract the second url

ColumnA(Page) - From CSV File
1. /www.thisite.com/audio-visual/,http://download.thisite.com2. /www.thisite.com/audio-visual/amps/series/product/

ColumnB (Clicked Page)
Function ClickedPage(sPage As String) As String
ClickedPage = Split(Page, ",")(0)
End Function

ColumnC (EndPage)
Function EndPage(sPage As String) As String
EndPage = Split(Page, ",")(1)
End Function

Problem
I keep getting an error for EndPage for situation where
/www.thisite.com/audio-visual/amps/series/product/

How do I modify the Function EndPage so that

If Split(Page, ",")(1) is empty, then "" .
Else EndPage = Split(Page, ",")(1)

I keep getting the error message "Subscript Out of Range"

[help_me_with_access]

extracting one page at a time? what for? why not just put the strings into an array and write a loop after it?

dim pages() as string
pages = split(pagestring, ",")

for i = 0 to ubound(page())
  'do
next i

is there something special about each url placement in the array? if you can sequence them it or patternize them, you can do the code looping and forget about the static code you're using now and extracting strings from specific parts of the array.

[nguyenak]

Hi,

Answers to question:
1. is there something special about each url placement in the array?
A: Yes, the first URL in the string represents the clicked Page.
The second URL represents the OutgoingLink. I only need the first 2 URLs. When I export from the analytics tool, its one string and I wanted to extract only specific parts of the array. Unfortunately, the string is not always clean and can have no deliminiters. For those situations, I dont need those parts.

2. For the above code, where do I put that in?
Basically, I wanted to extract the first part of the array, and the second part

thanks for your help!

[help_me_with_access]

well if the delimiter is not consistent, you're screwed. there's nothing you can do about that.

if the delimiter IS consistent however, use the above code and loop from 0 to 1 only. assign both array vals to whatever you want. that simple.!

[nguyenak]

Ok, so lets say it is consistent. Im not sure how the function looks. (Im sorry, Im a beginner learning on the fly)

Lets say its only to loop from 0 to 1. I've attached the access file.

[help_me_with_access]

ok, well aside from accessing the data, here is the coding:

public function getArrayStrings()

dim i as integer
dim arr() as string

arr = split(yourdata)

for i = 0 to 1
  'do something with the data
next i

end function

the rest you never specified on. e.g. - what to do with the data!!

[nguyenak]

2 Examples

Sheet1_result

Column (export) - These are the strings I import from CSV.
Column (Array0) - this is the value I want to extract from the string (from Column (export))
Column (Array1) - this is the value I want to extract from the string (from Column (export))

If there is no data for Array, then the field will be ""

Example2
Field 1 = Imported Fields from CSV
Field 2 = need a function to extract Array1
Field 3 = need a function to extract Array2

If it is an empty array, then it will be ""

When I use the Split Function in my previous code(mine, not the one you gave me), I keep getting this error message and it goes to debug.

I know you're doing the right thing, but Im not able to make it work for me.

[help_me_with_access]

well without seeing the code you're using, I can't do anything else for you. post it.

[nguyenak]

Attached is the DB.

Basically, the query shows "errors" and was hoping it would be NULL.

I really do appreciate your efforts!

[John_G]

You functions that you showed in your initial posts contain errors. You don't apply the Split function to the input argument - there is an "S" missing. they should read:

Function ClickedPage(sPage As String) As String
ClickedPage = Split(sPage, ",")(0)
End Function

and

Function EndPage(sPage As String) As String
EndPage = Split(sPage, ",")(1)
End Function

does that change things?

John

[nguyenak]

Hi John,

I already corrected it. Basically, Im hoping to get another approach so that an empty array is "" instead of "Error"(The "Subscript out of Range" appears) and the DB becomes too slow. I have over 100K Patterns like this.

But thank you for your response

You functions that you showed in your initial posts contain errors. You don't apply the Split function to the input argument - there is an "S" missing. they should read:

Function ClickedPage(sPage As String) As String
ClickedPage = Split(sPage, ",")(0)
End Function

and

Function EndPage(sPage As String) As String
EndPage = Split(sPage, ",")(1)
End Function

does that change things?

John

[John_G]

Hi -

try changing this:

EndPage = Split(sPage, ",")(1)

to this

endpage = ""
EndPage = if ubound(Split(sPage, ",")) > 0 then EndPage = Split(sPage, ",")(1)

That should eliminate the subscript error.

John

[nguyenak]

Here's the solution I found

Function TSplit(varInput As Variant, intNr As Integer) As String
Dim myArray() As String
myArray = Split(Nz(varInput, ""), ":") 'Choose your delimiter', use NZ function to return ""
TSplit= ""
If intNr <= UBound(myArray) Then TSplit= myArray(intNr)
End Function



try changing this:

EndPage = Split(sPage, ",")(1)

to this

endpage = ""
EndPage = if ubound(Split(sPage, ",")) > 0 then EndPage = Split(sPage, ",")(1)

That should eliminate the subscript error.

John[/QUOTE]