Solved

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

Posted on 2007-04-05
4
426 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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 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