IP Address Management System

J

jon

Hi,

Anyone knows how to input an IP address and then ensure
that i can sort it in the proper ascending or decending
order? what data type shld i use? am currently using
the "text" data type as its definitely not a number.
the dotted decimal form prevents me from sorting in the
correct order i need it in.

for eg. I have a block of 137.53.44.XXX addresses to
manage and i need to arrange the last 3 numbers in the
proper sequence.

pls help! thanks lots in advance! :)
jon
 
P

Paul Overway

Take the code below and paste it into a module. The function below is a
little more flexible than what was on the web page I referred you to:

In your query, create a new column with the following expression:

Token1: GetToken([YourFieldWithIPAddressHere],".",1)

Repeat for each token, changing the number/position.

You can then sort on the token(s). BTW...it isn't necessary that you show
the token columns.

'CODE START
Function GetToken(ByVal Arg As Variant, Separator As String, Indx As
Integer)
'Returns the nth word in a specific field.
Dim intStartPos As Integer, intEndPos As Integer
Dim intCount As Integer, intSepCount As Integer
Dim strTemp As String

For intCount = 1 To Len(Arg)
If Mid(Arg, intCount, 1) = Separator Then
intSepCount = intSepCount + 1
End If
Next

If Indx > intSepCount + 1 Then
GetToken = Null
Exit Function
End If

intStartPos = 1

For intCount = 2 To Indx
intStartPos = InStr(intStartPos, Arg, Separator) + 1
Next intCount

intEndPos = InStr(intStartPos, Arg, Separator) - 1

If intEndPos <= 0 Then intEndPos = Len(Arg)

GetToken = Trim(Mid(Arg, intStartPos, intEndPos - intStartPos + 1))

End Function
'CODE END
 
J

jon

damn you are good! :) appreciate all the help!

have done the module. no prob cutting and pasting.. :p
i assume access automatically calls the function from
there?

in my query of my table, i have tried to input in the
field row,
Token1: GetToken([IP Address],".",1) as "IP Address" is
the field name in the column of ip addresses in my table.

i get an error msg "Syntax error(comma)in query expression
'[IP Addr Table].[GetToken([IP Address],"].[",1)]'.

any idea how to solve this? thanks again!

ps:what do you mean by repeating for each token? I only
have one column of ip addresses and only the last 3
numbers are different.
pps: would love to tok to you over email. do drop by. :)

jon
-----Original Message-----
Take the code below and paste it into a module. The function below is a
little more flexible than what was on the web page I referred you to:

In your query, create a new column with the following expression:

Token1: GetToken([YourFieldWithIPAddressHere],".",1)

Repeat for each token, changing the number/position.

You can then sort on the token(s). BTW...it isn't necessary that you show
the token columns.

'CODE START
Function GetToken(ByVal Arg As Variant, Separator As String, Indx As
Integer)
'Returns the nth word in a specific field.
Dim intStartPos As Integer, intEndPos As Integer
Dim intCount As Integer, intSepCount As Integer
Dim strTemp As String

For intCount = 1 To Len(Arg)
If Mid(Arg, intCount, 1) = Separator Then
intSepCount = intSepCount + 1
End If
Next

If Indx > intSepCount + 1 Then
GetToken = Null
Exit Function
End If

intStartPos = 1

For intCount = 2 To Indx
intStartPos = InStr(intStartPos, Arg, Separator) + 1
Next intCount

intEndPos = InStr(intStartPos, Arg, Separator) - 1

If intEndPos <= 0 Then intEndPos = Len(Arg)

GetToken = Trim(Mid(Arg, intStartPos, intEndPos - intStartPos + 1))

End Function
'CODE END

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


jon said:
thanks! but where and how do i input the code? is it a
macro? do i need to create a assess page to do it?
Click to expand...


.
Click to expand...
 
B

Brendan Reynolds \(MVP\)

When I did this, I used four Integer fields for the four parts of the
address. Just concatenate them as necessary for display.
 
P

Paul Overway

Oops...should have done it this way:

Token1: CInt(GetToken([IP Addr Table].[IP Address],'.',1))

Paste this into field row exactly as shown.

Assuming the IP address is 192.168.1.2, this will give you 198 in a column
by itself.

Token2: CInt(GetToken([IP Addr Table].[IP Address],'.',2)) will give you
168...repeat for other columns

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


jon said:
damn you are good! :) appreciate all the help!

have done the module. no prob cutting and pasting.. :p
i assume access automatically calls the function from
there?

in my query of my table, i have tried to input in the
field row,
Token1: GetToken([IP Address],".",1) as "IP Address" is
the field name in the column of ip addresses in my table.

i get an error msg "Syntax error(comma)in query expression
'[IP Addr Table].[GetToken([IP Address],"].[",1)]'.

any idea how to solve this? thanks again!

ps:what do you mean by repeating for each token? I only
have one column of ip addresses and only the last 3
numbers are different.
pps: would love to tok to you over email. do drop by. :)

jon
-----Original Message-----
Take the code below and paste it into a module. The function below is a
little more flexible than what was on the web page I referred you to:

In your query, create a new column with the following expression:

Token1: GetToken([YourFieldWithIPAddressHere],".",1)

Repeat for each token, changing the number/position.

You can then sort on the token(s). BTW...it isn't necessary that you show
the token columns.

'CODE START
Function GetToken(ByVal Arg As Variant, Separator As String, Indx As
Integer)
'Returns the nth word in a specific field.
Dim intStartPos As Integer, intEndPos As Integer
Dim intCount As Integer, intSepCount As Integer
Dim strTemp As String

For intCount = 1 To Len(Arg)
If Mid(Arg, intCount, 1) = Separator Then
intSepCount = intSepCount + 1
End If
Next

If Indx > intSepCount + 1 Then
GetToken = Null
Exit Function
End If

intStartPos = 1

For intCount = 2 To Indx
intStartPos = InStr(intStartPos, Arg, Separator) + 1
Next intCount

intEndPos = InStr(intStartPos, Arg, Separator) - 1

If intEndPos <= 0 Then intEndPos = Len(Arg)

GetToken = Trim(Mid(Arg, intStartPos, intEndPos - intStartPos + 1))

End Function
'CODE END

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


jon said:
thanks! but where and how do i input the code? is it a
macro? do i need to create a assess page to do it?

-----Original Message-----
You need to tokenize the string, and then sort on the
tokens (as numeric).
You are doing right to store in text data type...really
no choice in the
matter.

See http://www.mvps.org/access/strings/str0003.htm for
example of how to
tokenize strings.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


Hi,

Anyone knows how to input an IP address and then ensure
that i can sort it in the proper ascending or decending
order? what data type shld i use? am currently using
the "text" data type as its definitely not a number.
the dotted decimal form prevents me from sorting in the
correct order i need it in.

for eg. I have a block of 137.53.44.XXX addresses to
manage and i need to arrange the last 3 numbers in the
proper sequence.

pls help! thanks lots in advance! :)
jon


.
Click to expand...


.
Click to expand...
Click to expand...
 
J

jon

hi brendan. :)

how do you actually concatenate them? do the decimals
places show up?

thanks!
jon
 
P

Paul Overway

The expresion should be pasted into a query. I've tested this, and it works
fine. Assuming your table is name IP Addr Table, and the field is named IP
Address...there is nothing to change, paste it exactly as is! If you;re
getting a syntax error....you are changing things that don't need to be
changed.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


jon said:
I'm still getting a syntax error paul.. :(

tried changing the '.' to "." for string? but still doesnt
work...

save me...
jon
-----Original Message-----
Oops...should have done it this way:

Token1: CInt(GetToken([IP Addr Table].[IP Address],'.',1))

Paste this into field row exactly as shown.

Assuming the IP address is 192.168.1.2, this will give you 198 in a column
by itself.

Token2: CInt(GetToken([IP Addr Table].[IP Address],'.',2)) will give you
168...repeat for other columns

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


jon said:
damn you are good! :) appreciate all the help!

have done the module. no prob cutting and pasting.. :p
i assume access automatically calls the function from
there?

in my query of my table, i have tried to input in the
field row,
Token1: GetToken([IP Address],".",1) as "IP Address" is
the field name in the column of ip addresses in my table.

i get an error msg "Syntax error(comma)in query expression
'[IP Addr Table].[GetToken([IP Address],"].[",1)]'.

any idea how to solve this? thanks again!

ps:what do you mean by repeating for each token? I only
have one column of ip addresses and only the last 3
numbers are different.
pps: would love to tok to you over email. do drop by. :)

jon

-----Original Message-----
Take the code below and paste it into a module. The
function below is a
little more flexible than what was on the web page I
referred you to:

In your query, create a new column with the following
expression:

Token1: GetToken([YourFieldWithIPAddressHere],".",1)

Repeat for each token, changing the number/position.

You can then sort on the token(s). BTW...it isn't
necessary that you show
the token columns.

'CODE START
Function GetToken(ByVal Arg As Variant, Separator As
String, Indx As
Integer)
'Returns the nth word in a specific field.
Dim intStartPos As Integer, intEndPos As Integer
Dim intCount As Integer, intSepCount As Integer
Dim strTemp As String

For intCount = 1 To Len(Arg)
If Mid(Arg, intCount, 1) = Separator Then
intSepCount = intSepCount + 1
End If
Next

If Indx > intSepCount + 1 Then
GetToken = Null
Exit Function
End If

intStartPos = 1

For intCount = 2 To Indx
intStartPos = InStr(intStartPos, Arg, Separator)
+ 1
Next intCount

intEndPos = InStr(intStartPos, Arg, Separator) - 1

If intEndPos <= 0 Then intEndPos = Len(Arg)

GetToken = Trim(Mid(Arg, intStartPos, intEndPos -
intStartPos + 1))

End Function
'CODE END

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


thanks! but where and how do i input the code? is it a
macro? do i need to create a assess page to do it?

-----Original Message-----
You need to tokenize the string, and then sort on the
tokens (as numeric).
You are doing right to store in text data type...really
no choice in the
matter.

See http://www.mvps.org/access/strings/str0003.htm for
example of how to
tokenize strings.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


Hi,

Anyone knows how to input an IP address and then
ensure
that i can sort it in the proper ascending or
decending
order? what data type shld i use? am currently using
the "text" data type as its definitely not a number.
the dotted decimal form prevents me from sorting in
the
correct order i need it in.

for eg. I have a block of 137.53.44.XXX addresses to
manage and i need to arrange the last 3 numbers in
the
proper sequence.

pls help! thanks lots in advance! :)
jon


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
J

jon

I get /*Syntax error in query expression
'[IP Addr Table].[CInt(GetToken([IP Addr Table]].[[IP
Address],"].[",1))]'.*/

-----Original Message-----
I'm still getting a syntax error paul.. :(

tried changing the '.' to "." for string? but still doesnt
work...

save me...
jon
-----Original Message-----
Oops...should have done it this way:

Token1: CInt(GetToken([IP Addr Table].[IP Address],'.',1))

Paste this into field row exactly as shown.

Assuming the IP address is 192.168.1.2, this will give you 198 in a column
by itself.

Token2: CInt(GetToken([IP Addr Table].[IP Address],'.',2)) will give you
168...repeat for other columns

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


jon said:
damn you are good! :) appreciate all the help!

have done the module. no prob cutting and pasting.. :p
i assume access automatically calls the function from
there?

in my query of my table, i have tried to input in the
field row,
Token1: GetToken([IP Address],".",1) as "IP Address" is
the field name in the column of ip addresses in my table.

i get an error msg "Syntax error(comma)in query expression
'[IP Addr Table].[GetToken([IP Address],"].[",1)]'.

any idea how to solve this? thanks again!

ps:what do you mean by repeating for each token? I only
have one column of ip addresses and only the last 3
numbers are different.
pps: would love to tok to you over email. do drop by. :)

jon

-----Original Message-----
Take the code below and paste it into a module. The
function below is a
little more flexible than what was on the web page I
referred you to:

In your query, create a new column with the following
expression:

Token1: GetToken([YourFieldWithIPAddressHere],".",1)

Repeat for each token, changing the number/position.

You can then sort on the token(s). BTW...it isn't
necessary that you show
the token columns.

'CODE START
Function GetToken(ByVal Arg As Variant, Separator As
String, Indx As
Integer)
'Returns the nth word in a specific field.
Dim intStartPos As Integer, intEndPos As Integer
Dim intCount As Integer, intSepCount As Integer
Dim strTemp As String

For intCount = 1 To Len(Arg)
If Mid(Arg, intCount, 1) = Separator Then
intSepCount = intSepCount + 1
End If
Next

If Indx > intSepCount + 1 Then
GetToken = Null
Exit Function
End If

intStartPos = 1

For intCount = 2 To Indx
intStartPos = InStr(intStartPos, Arg, Separator)
+ 1
Next intCount

intEndPos = InStr(intStartPos, Arg, Separator) - 1

If intEndPos <= 0 Then intEndPos = Len(Arg)

GetToken = Trim(Mid(Arg, intStartPos, intEndPos -
intStartPos + 1))

End Function
'CODE END

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


thanks! but where and how do i input the code? is
Click to expand...
Click to expand...
it
a
Click to expand...
addresses
to


.
Click to expand...
.
Click to expand...
 
P

Paul Overway

UGH....that isn't what I told you to paste. Is it? Paste expression
below....NOTHING ELSE....DON'T CHANGE ANYTHING

CInt(GetToken([IP Addr Table]].[[IP Address],".",1))



--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


jon said:
I get /*Syntax error in query expression
'[IP Addr Table].[CInt(GetToken([IP Addr Table]].[[IP
Address],"].[",1))]'.*/

-----Original Message-----
I'm still getting a syntax error paul.. :(

tried changing the '.' to "." for string? but still doesnt
work...

save me...
jon
-----Original Message-----
Oops...should have done it this way:

Token1: CInt(GetToken([IP Addr Table].[IP Address],'.',1))

Paste this into field row exactly as shown.

Assuming the IP address is 192.168.1.2, this will give you 198 in a column
by itself.

Token2: CInt(GetToken([IP Addr Table].[IP Address],'.',2)) will give you
168...repeat for other columns

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


damn you are good! :) appreciate all the help!

have done the module. no prob cutting and pasting.. :p
i assume access automatically calls the function from
there?

in my query of my table, i have tried to input in the
field row,
Token1: GetToken([IP Address],".",1) as "IP Address" is
the field name in the column of ip addresses in my table.

i get an error msg "Syntax error(comma)in query expression
'[IP Addr Table].[GetToken([IP Address],"].[",1)]'.

any idea how to solve this? thanks again!

ps:what do you mean by repeating for each token? I only
have one column of ip addresses and only the last 3
numbers are different.
pps: would love to tok to you over email. do drop by. :)

jon

-----Original Message-----
Take the code below and paste it into a module. The
function below is a
little more flexible than what was on the web page I
referred you to:

In your query, create a new column with the following
expression:

Token1: GetToken([YourFieldWithIPAddressHere],".",1)

Repeat for each token, changing the number/position.

You can then sort on the token(s). BTW...it isn't
necessary that you show
the token columns.

'CODE START
Function GetToken(ByVal Arg As Variant, Separator As
String, Indx As
Integer)
'Returns the nth word in a specific field.
Dim intStartPos As Integer, intEndPos As Integer
Dim intCount As Integer, intSepCount As Integer
Dim strTemp As String

For intCount = 1 To Len(Arg)
If Mid(Arg, intCount, 1) = Separator Then
intSepCount = intSepCount + 1
End If
Next

If Indx > intSepCount + 1 Then
GetToken = Null
Exit Function
End If

intStartPos = 1

For intCount = 2 To Indx
intStartPos = InStr(intStartPos, Arg, Separator)
+ 1
Next intCount

intEndPos = InStr(intStartPos, Arg, Separator) - 1

If intEndPos <= 0 Then intEndPos = Len(Arg)

GetToken = Trim(Mid(Arg, intStartPos, intEndPos -
intStartPos + 1))

End Function
'CODE END

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


thanks! but where and how do i input the code? is
Click to expand...
it
a
macro? do i need to create a assess page to do it?

-----Original Message-----
You need to tokenize the string, and then sort on the
tokens (as numeric).
You are doing right to store in text data type...really
no choice in the
matter.

See http://www.mvps.org/access/strings/str0003.htm for
example of how to
tokenize strings.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


Hi,

Anyone knows how to input an IP address and then
ensure
that i can sort it in the proper ascending or
decending
order? what data type shld i use? am currently using
the "text" data type as its definitely not a number.
the dotted decimal form prevents me from sorting in
the
correct order i need it in.

for eg. I have a block of 137.53.44.XXX
Click to expand...
addresses
to
manage and i need to arrange the last 3 numbers in
the
proper sequence.

pls help! thanks lots in advance! :)
jon


.



.



.
Click to expand...
.
Click to expand...
Click to expand...
 
P

Paul Overway

Just to be perfectly clear...whatever column you paste the expression into,
the field row should be EMPTY!

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


jon said:
I get /*Syntax error in query expression
'[IP Addr Table].[CInt(GetToken([IP Addr Table]].[[IP
Address],"].[",1))]'.*/

-----Original Message-----
I'm still getting a syntax error paul.. :(

tried changing the '.' to "." for string? but still doesnt
work...

save me...
jon
-----Original Message-----
Oops...should have done it this way:

Token1: CInt(GetToken([IP Addr Table].[IP Address],'.',1))

Paste this into field row exactly as shown.

Assuming the IP address is 192.168.1.2, this will give you 198 in a column
by itself.

Token2: CInt(GetToken([IP Addr Table].[IP Address],'.',2)) will give you
168...repeat for other columns

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


damn you are good! :) appreciate all the help!

have done the module. no prob cutting and pasting.. :p
i assume access automatically calls the function from
there?

in my query of my table, i have tried to input in the
field row,
Token1: GetToken([IP Address],".",1) as "IP Address" is
the field name in the column of ip addresses in my table.

i get an error msg "Syntax error(comma)in query expression
'[IP Addr Table].[GetToken([IP Address],"].[",1)]'.

any idea how to solve this? thanks again!

ps:what do you mean by repeating for each token? I only
have one column of ip addresses and only the last 3
numbers are different.
pps: would love to tok to you over email. do drop by. :)

jon

-----Original Message-----
Take the code below and paste it into a module. The
function below is a
little more flexible than what was on the web page I
referred you to:

In your query, create a new column with the following
expression:

Token1: GetToken([YourFieldWithIPAddressHere],".",1)

Repeat for each token, changing the number/position.

You can then sort on the token(s). BTW...it isn't
necessary that you show
the token columns.

'CODE START
Function GetToken(ByVal Arg As Variant, Separator As
String, Indx As
Integer)
'Returns the nth word in a specific field.
Dim intStartPos As Integer, intEndPos As Integer
Dim intCount As Integer, intSepCount As Integer
Dim strTemp As String

For intCount = 1 To Len(Arg)
If Mid(Arg, intCount, 1) = Separator Then
intSepCount = intSepCount + 1
End If
Next

If Indx > intSepCount + 1 Then
GetToken = Null
Exit Function
End If

intStartPos = 1

For intCount = 2 To Indx
intStartPos = InStr(intStartPos, Arg, Separator)
+ 1
Next intCount

intEndPos = InStr(intStartPos, Arg, Separator) - 1

If intEndPos <= 0 Then intEndPos = Len(Arg)

GetToken = Trim(Mid(Arg, intStartPos, intEndPos -
intStartPos + 1))

End Function
'CODE END

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


thanks! but where and how do i input the code? is
Click to expand...
it
a
macro? do i need to create a assess page to do it?

-----Original Message-----
You need to tokenize the string, and then sort on the
tokens (as numeric).
You are doing right to store in text data type...really
no choice in the
matter.

See http://www.mvps.org/access/strings/str0003.htm for
example of how to
tokenize strings.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


Hi,

Anyone knows how to input an IP address and then
ensure
that i can sort it in the proper ascending or
decending
order? what data type shld i use? am currently using
the "text" data type as its definitely not a number.
the dotted decimal form prevents me from sorting in
the
correct order i need it in.

for eg. I have a block of 137.53.44.XXX
Click to expand...
addresses
to
manage and i need to arrange the last 3 numbers in
the
proper sequence.

pls help! thanks lots in advance! :)
jon


.



.



.
Click to expand...
.
Click to expand...
Click to expand...
 
J

jon

did exactly as you told me. i'm a good boy. :)

getting a compile error this time. is CInt() a library
function? in my module do i need to include anything other
than the code you asked me to paste?
-----Original Message-----
The expresion should be pasted into a query. I've tested this, and it works
fine. Assuming your table is name IP Addr Table, and the field is named IP
Address...there is nothing to change, paste it exactly as is! If you;re
getting a syntax error....you are changing things that don't need to be
changed.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


jon said:
I'm still getting a syntax error paul.. :(

tried changing the '.' to "." for string? but still doesnt
work...

save me...
jon
-----Original Message-----
Oops...should have done it this way:

Token1: CInt(GetToken([IP Addr Table].[IP Address],'.',1))

Paste this into field row exactly as shown.

Assuming the IP address is 192.168.1.2, this will give you 198 in a column
by itself.

Token2: CInt(GetToken([IP Addr Table].[IP Address],'.',2)) will give you
168...repeat for other columns

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


damn you are good! :) appreciate all the help!

have done the module. no prob cutting and pasting.. :p
i assume access automatically calls the function from
there?

in my query of my table, i have tried to input in the
field row,
Token1: GetToken([IP Address],".",1) as "IP Address" is
the field name in the column of ip addresses in my table.

i get an error msg "Syntax error(comma)in query expression
'[IP Addr Table].[GetToken([IP Address],"].[",1)]'.

any idea how to solve this? thanks again!

ps:what do you mean by repeating for each token? I only
have one column of ip addresses and only the last 3
numbers are different.
pps: would love to tok to you over email. do drop by. :)

jon

-----Original Message-----
Take the code below and paste it into a module. The
function below is a
little more flexible than what was on the web page I
referred you to:

In your query, create a new column with the following
expression:

Token1: GetToken([YourFieldWithIPAddressHere],".",1)

Repeat for each token, changing the number/position.

You can then sort on the token(s). BTW...it isn't
necessary that you show
the token columns.

'CODE START
Function GetToken(ByVal Arg As Variant, Separator As
String, Indx As
Integer)
'Returns the nth word in a specific field.
Dim intStartPos As Integer, intEndPos As Integer
Dim intCount As Integer, intSepCount As Integer
Dim strTemp As String

For intCount = 1 To Len(Arg)
If Mid(Arg, intCount, 1) = Separator Then
intSepCount = intSepCount + 1
End If
Next

If Indx > intSepCount + 1 Then
GetToken = Null
Exit Function
End If

intStartPos = 1

For intCount = 2 To Indx
intStartPos = InStr(intStartPos, Arg, Separator)
+ 1
Next intCount

intEndPos = InStr(intStartPos, Arg, Separator) - 1

If intEndPos <= 0 Then intEndPos = Len(Arg)

GetToken = Trim(Mid(Arg, intStartPos, intEndPos -
intStartPos + 1))

End Function
'CODE END

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


thanks! but where and how do i input the code? is
Click to expand...
it
a
macro? do i need to create a assess page to do it?

-----Original Message-----
You need to tokenize the string, and then sort on the
tokens (as numeric).
You are doing right to store in text data type...really
no choice in the
matter.

See
Click to expand...
http://www.mvps.org/access/strings/str0003.htm
for
example of how to
tokenize strings.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


Hi,

Anyone knows how to input an IP address and then
ensure
that i can sort it in the proper ascending or
decending
order? what data type shld i use? am currently using
the "text" data type as its definitely not a number.
the dotted decimal form prevents me from
Click to expand...
sorting
in
the
correct order i need it in.

for eg. I have a block of 137.53.44.XXX
Click to expand...
addresses
to
manage and i need to arrange the last 3 numbers in
the
proper sequence.

pls help! thanks lots in advance! :)
jon


.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
P

Paul Overway

CInt is a native VBA function. VBA library is referenced by default, and
you can't change it. Lines with compile errors are red...they also get
highlighted. If you can't compile, watch line wrap in pasted code....Indx
As Integer) should be on same line as Function. If you still can't
compile, your system is hosed.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


jon said:
did exactly as you told me. i'm a good boy. :)

getting a compile error this time. is CInt() a library
function? in my module do i need to include anything other
than the code you asked me to paste?
-----Original Message-----
The expresion should be pasted into a query. I've tested this, and it works
fine. Assuming your table is name IP Addr Table, and the field is named IP
Address...there is nothing to change, paste it exactly as is! If you;re
getting a syntax error....you are changing things that don't need to be
changed.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


jon said:
I'm still getting a syntax error paul.. :(

tried changing the '.' to "." for string? but still doesnt
work...

save me...
jon

-----Original Message-----
Oops...should have done it this way:

Token1: CInt(GetToken([IP Addr Table].[IP Address],'.',1))

Paste this into field row exactly as shown.

Assuming the IP address is 192.168.1.2, this will give
you 198 in a column
by itself.

Token2: CInt(GetToken([IP Addr Table].[IP
Address],'.',2)) will give you
168...repeat for other columns

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


damn you are good! :) appreciate all the help!

have done the module. no prob cutting and pasting.. :p
i assume access automatically calls the function from
there?

in my query of my table, i have tried to input in the
field row,
Token1: GetToken([IP Address],".",1) as "IP Address" is
the field name in the column of ip addresses in my
table.

i get an error msg "Syntax error(comma)in query
expression
'[IP Addr Table].[GetToken([IP Address],"].[",1)]'.

any idea how to solve this? thanks again!

ps:what do you mean by repeating for each token? I only
have one column of ip addresses and only the last 3
numbers are different.
pps: would love to tok to you over email. do drop by. :)

jon

-----Original Message-----
Take the code below and paste it into a module. The
function below is a
little more flexible than what was on the web page I
referred you to:

In your query, create a new column with the following
expression:

Token1: GetToken([YourFieldWithIPAddressHere],".",1)

Repeat for each token, changing the number/position.

You can then sort on the token(s). BTW...it isn't
necessary that you show
the token columns.

'CODE START
Function GetToken(ByVal Arg As Variant, Separator As
String, Indx As
Integer)
'Returns the nth word in a specific field.
Dim intStartPos As Integer, intEndPos As Integer
Dim intCount As Integer, intSepCount As Integer
Dim strTemp As String

For intCount = 1 To Len(Arg)
If Mid(Arg, intCount, 1) = Separator Then
intSepCount = intSepCount + 1
End If
Next

If Indx > intSepCount + 1 Then
GetToken = Null
Exit Function
End If

intStartPos = 1

For intCount = 2 To Indx
intStartPos = InStr(intStartPos, Arg,
Separator)
+ 1
Next intCount

intEndPos = InStr(intStartPos, Arg, Separator) - 1

If intEndPos <= 0 Then intEndPos = Len(Arg)

GetToken = Trim(Mid(Arg, intStartPos, intEndPos -
intStartPos + 1))

End Function
'CODE END

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


thanks! but where and how do i input the code? is it
a
macro? do i need to create a assess page to do it?

-----Original Message-----
You need to tokenize the string, and then sort on
the
tokens (as numeric).
You are doing right to store in text data
type...really
no choice in the
matter.

See http://www.mvps.org/access/strings/str0003.htm
for
example of how to
tokenize strings.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


message
Hi,

Anyone knows how to input an IP address and then
ensure
that i can sort it in the proper ascending or
decending
order? what data type shld i use? am currently
using
the "text" data type as its definitely not a
number.
the dotted decimal form prevents me from sorting
in
the
correct order i need it in.

for eg. I have a block of 137.53.44.XXX addresses
to
manage and i need to arrange the last 3 numbers in
the
proper sequence.

pls help! thanks lots in advance! :)
jon


.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
B

Brendan Reynolds \(MVP\)

In query design view ...

IPAddress: [IPA] & "." & [IPB] & "." & [IPC] & "." & [IPD]

.... where IPA, IPB, etc., are the names of the four fields.

In the ControlSource of a text box on a form or report ...

=[IPA] & "." & [IPB] & ". " & [IPC] & "." & [IPD]
 
J

jon

thanks! appreciate the help! :)
-----Original Message-----
In query design view ...

IPAddress: [IPA] & "." & [IPB] & "." & [IPC] & "." & [IPD]

.... where IPA, IPB, etc., are the names of the four fields.

In the ControlSource of a text box on a form or report ...

=[IPA] & "." & [IPB] & ". " & [IPC] & "." & [IPD]

--
Brendan Reynolds (Access MVP)
(e-mail address removed)

jon said:
hi brendan. :)

how do you actually concatenate them? do the decimals
places show up?

thanks!
jon four
parts of the
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IP Address Sorting 7
Count IP Addresses 3
Script to find IP Third Octave in IP Address 3
Creating custom number formats 1
"Free" ip address in asset database, not remove? 0
Windows XP Dhcp Your computer has lost the lease to its IP address 192.168.1.2 6
importing IP addresses 1
Sorting IP Addresses in Excel? 6

Top