Solved

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

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 67
Accessing variables in MySQL query 4 52
MS SQL Conditional WHERE clause 3 36
Syntax issue with my Where Clause SQL 2012 20 38
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.…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

679 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