Solved

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

Posted on 2007-04-05
4
427 Views
Last Modified: 2011-09-20
Is there a way in Access 2002 Query to split an IP Address into octets?  if [IP_Address} contains   192.168.10.1
So the result would show new columns as below
[A] = 192  [B] = 168   [C] = 10

Many thanks
0
Comment
Question by:westermo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 125 total points
ID: 18857093
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
eg


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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18857111
The fixed approach, not a good idea if u know the values will differ eg
192.168.100.1 will produce the same result as 192.168.10.1
here is the fixed approach

select Left$(myIpField, 3), Mid$(myIpField, 5, 3), Mid$(myIpField, 9, 2)
from mytable
0
 
LVL 2

Author Comment

by:westermo
ID: 18857174
Simply brilliant.  Thank you Rockiroads
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18857205
No probs, glad to have helped
0

Featured Post

Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question