Split Function help

[Guest_imported]

Hi all,

I have a really long field called "Message" in my database. Below is the sample out put of it:

"id=firewall sn=00401012401B time=""2002-04-02 17:17:50"" fw=216.191.177.98 pri=6 c=1024 m=98 n=139575 src=10.0.1.1:53:LAN dst=206.191.82.255:53:WAN proto=udp/dns sent=54 37:WANult=304 dstname=adiscon.orgarg=/Seminars/RuleEngine_files/audio%20slides%20html%20toc%20resiz...
"

As you can see that its a big field. Out of this field I am extracting the src IP address. In the above case it would be src=10.0.1.1
I want to extract this IP address, in other words thats the only data I am concerned with. I have tried the split function and below is my code.

_________________________________________________________
Option Compare Database
Option Explicit

Sub EventsMessage()
Dim cnnlocal As ADODB.Connection
Dim cmdlocal As ADODB.Command
Dim Events As ADODB.Recordset
Dim intUpdate As Integer
Dim str As Integer
Dim i As Integer


Set cnnlocal = New ADODB.Connection

cnnlocal.ConnectionString = "DSN=Wsyslog"
cnnlocal.Open

Set cmdlocal = New ADODB.Command
Set cmdlocal.ActiveConnection = cnnlocal
cmdlocal.CommandText = "Select * from Info"
Events.CursorType = adOpenForwardOnly
Set Events = cmdlocal.Execute

intUpdate = 0



Do While Not Events.EOF
intUpdate = intUpdate + 1
str = Events.Fields("Message&quot
str = Split(Trim(str), " &quot

Events.Fields("Message&quot = Split(Trim(str), " &quot

If Left(str(i), 4) = "src=" Then
str = Left(str(i), InStr(1, str(i), ":&quot - 1)
End If
Events.Fields("Message&quot = str

Events.Update
Events.MoveNext
Loop
Debug.Print intUpdated & " Records Updated"
Events.Close

End Sub
_________________________________________________________

I get the error saying "Compile Error; Expected Variable or Procudure Not Module." It then highlights the Split in str = Split(Trim(str), " &quot. I am using Access 97 with VBA. My guess is that Split is not a recognized command. Can any one help me with this code or suggest a better one???

 

[EvilCabal]

I'm not quite sure about the error you get but one thing is wrong for sure : you are assigning the result of split to an integer instead of an array of string.

"Dim str() as string" instead of "Dim str as integer" might solve your problem.

 

[FancyPrairie]

I just learned of the Split function yesterday. So I'm not sure. However, in your case I would just use the Instr function:

i = instr(YourString,"src=&quot
if (i > 0) then
k = instr(i,YourString,":&quot
if (k > 0) then
strIPAddress = mid(YourString,i+4,k-(i+4))
end if
end if

 

[hermanlaksko]

If I understand you correct you only want the "src" info if this is correct then try this:

Dim Rpl, YrTxt
YrTxt=YourVeryLooooong field ;-)

Rpl= Mid(YrTxt, InStr(1, YrTxt, "src&quot + 4, 17)
Rpl= Left(Rpl, InStr(1, Rpl, ":&quot - 1)

This will give you "10.0.1.1" - in this case

 

[JoanneM]

Just thought while I'm here this may be another alternative
copy this into a module and check it out

you could turn it into a function if you wanted to return the ip I suppose

Sub ip()
Dim a As Variant
Dim st As Integer
Dim endstr As Integer
Dim ips As String
a = "id=firewall sn=00401012401B time=""2002-04-02 17:17:50"" fw=216.191.177.98 pri=6 c=1024 m=98 n=139575 src=10.0.1.1:53:LAN dst=206.191.82.255:53:WAN proto=udp/dns sent=54 37:WANult=304 dstname=adiscon.orgarg=/Seminars/RuleEngine_files/audio%20slides%20html%20toc%20resiz..."
st = InStr(1, a, "fw=&quot
endstr = InStr(1, a, "pri=&quot

ips = Mid(a, st + 3, endstr - st - 3)

End Sub

regards jo

 

[Guest_imported]

Thanks guys for ur help.

How ever I am dealing with actual fields. I just copied and pasted the value from the field. It all seems to work except now I am having connectivity problems. The code breaks at where it says cnnLocal.open.

The error it gives is "Data source name not found and no default driver speciefied."