I imported a log file into access and am trying to parse out the data to a new table. I have been using the Instr and Mid function in a Query just fine. However, I could not get the second or third comma seperated field to get pulled so I created a function to do the job for me.

The problem is that although my declared variables have the correct data for pulling the fields I want (I tested the function by having it just return the variable value) it will not work together in the mid function.

The problem seems to be the intEnd value. If I just put in the value of "2" for example it works fine but it will not work with the intEnd in the formula.

I get a Run-time error 5: Invalid procedure call or argument

I have run out of ideas. Can anyone out there help me?

Here is the function:

Public Function SourcePort(ByVal strField As String) As String
Dim intFirst As Integer
Dim intSecond As Integer
Dim intEnd As Integer

intFirst = InStr(1, strField, ",") + 2
intSecond = InStr(intFirst, strField, ",")
intEnd = intSecond - intFirst

SourcePort = Mid(strField, intFirst, intSecond - intFirst)
End Function

That would be an error that you could trap, which is most likely the problem,
since the mid and left, and right functions can't process negative numbers.

That might occur if the intSecond is set to zero at some point. Try running this code and then check your immediate window for results

Public Function SourcePort(ByVal strField As String) As String
Dim intFirst As Single
Dim intSecond As Single
Dim intEnd As Single

intFirst = InStr(1, strField, ",") + 2
intSecond = InStr(intFirst, strField, ",")
intEnd = intSecond - intFirst

debug.Print intFirst
debug.Print intSecond
debug.Print intEnd

If intEnd < 0 Then
SourcePoint = "NegativeLength"
Else
SourcePort = Mid(strField, intFirst, intSecond - intFirst)
End If

End Function





sportsguy

If you still get the error, then the function would not work with a ZERO as one of the integers.

sportsguy

It must of had a negative issue with one of the records because it worked as soon as a verified a positive number.

Thanks a million Sportsguy

could you post a sample line of what the data looks like... you could try a different way to get your variables not using mid. For example:

Public Function SourcePort(ByVal strField As String) As String

Dim commaPos As Integer 'delimiter position
Dim str1 As String
Dim str2 As String
str1 = ""
str2 = ""

commaPos = 1
str2 = strField
Do While commaPos > 0
commaPos = InStr(1, str2, ",", vbTextCompare)

If commaPos <> 0 And IsNull(addSep) = False Then
str1 = Left$(str2, commaPos - 1)
str2 = Right$(str2, Len(str2) - commaPos)
Debug.Print str1
End If
Loop

If IsNull(str2) = True Or str2 = "" Then
Else
Debug.Print str2
End If

End Function


If you really want a better way then search for VBScript.RegExp on this site.. or look it up on windowsitpro.com or google. It is a good way to look for patterns and other such helpful functions for imports.

I already broke the text file out the best it would with delimiters but some fields still needed more work. Here is a sample of the one I was working on.

Source: 216.109.112.135, 80, WAN -

I need to get the ip address port number and source seperated like thus:

192.168.10.7
25
Lan

I need to get the ip address port number and source seperated like thus:

192.168.10.7
25
Lan

Did you try my above function?