Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

914 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now