• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

Access 2002 Query to obtain Octet values from an IP Address field

Is there a way in Access 2002 Query to split an IP Address into octets?  if [IP_Address} contains
So the result would show new columns as below
[A] = 192  [B] = 168   [C] = 10

Many thanks
  • 3
1 Solution
if its fixed positions then u can use a combination of left and mid

or u can create a vba function if u cannot guarantee it being in the same pos

public function GetPartIP(byval sIP as String, byval iOctet as integer) as String

    dim sVals() as String

    sVals = split(sIP,".")
    GetPartIP = sVals(iOctet-1)
end function

then in your query u would do

select GetpartIP(myipfield,1) as FirstNode, GetPartIP(myipfield,2) AS SecondNode, GetPartIP(myipfield,3) as thirdNode
from mytable
The fixed approach, not a good idea if u know the values will differ eg will produce the same result as
here is the fixed approach

select Left$(myIpField, 3), Mid$(myIpField, 5, 3), Mid$(myIpField, 9, 2)
from mytable
westermoAuthor Commented:
Simply brilliant.  Thank you Rockiroads
No probs, glad to have helped
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now