IP address as data type

[kerlawk]

i'm looking to import a large amount of data from an excel spreadsheet, where one of the columns is an ip range
ie: 255.255.255.0 - 255.255.255.255

i would like to be able to sett up a query to search this range to see if a particular ip falls within this range, and if so diplay the correct info.

my question: is this possible? (i think it is)
and if so, what would be the best way to approach this?

Any ideas would be GREATLY appreciated as i'm currently stuck. Should i try something different?

Thank you very much

 

[Bastien]

what about converting the string containing the IP to a number (is strip out the decimals and combine the parts into one string then CInt(string) to convert to a number? it would make comparison very easy.

hth Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[kerlawk]

Great suggestion!!!

The major problem i see with that is the size of the numbers will vary for example:

10.0.10.1 - 10.0.255.255

stripped it would be

100101 - 100255255

Is there a way to insert zeros before the number if the number is less than 3? Then it would be

01000000100001 - 010000255255

then i think the search would work, shouldn't it?

Anyone have any ideas on if this would work / how to do it?

Thanks again

 

[Bastien]

if len(strIp)<10 then
strIp = &quot;000&quot; + strIP
end if
Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[scriverb]

For demo purposes created a form with four text boxes on it. IPlow, IPhigh, ConvIPlow, ConvIPhigh. Also, create a CommandButton called ConvIPAddress. Paste the below code into the OnClick event procedure. This will convert the IPlow and IPhigh to ConvIPlow and ConvIPhigh in the format requested. No decimals with zero filled for each number between the original decimals.

Private Sub ConvIPAddress_Click()
Dim i As Integer
Dim vIPString As String
vIPString = Me![IPlow]
For i = 1 To 4
If i < 4 Then
Me![ConvIPlow] = Me![ConvIPlow] & Format(Mid$(vIPString, 1, InStr(1, vIPString, &quot;.&quot - 1), &quot;000&quot
vIPString = Mid$(vIPString, InStr(1, vIPString, &quot;.&quot + 1)
Else
Me![ConvIPlow] = Me![ConvIPlow] & Format(vIPString, &quot;000&quot
End If
Next i
vIPString = Me![IPhigh]
For i = 1 To 4
If i < 4 Then
Me![ConvIPhigh] = Me![ConvIPhigh] & Format(Mid$(vIPString, 1, InStr(1, vIPString, &quot;.&quot - 1), &quot;000&quot
vIPString = Mid$(vIPString, InStr(1, vIPString, &quot;.&quot + 1)
Else
Me![ConvIPhigh] = Me![ConvIPhigh] & Format(vIPString, &quot;000&quot
End If
Next i
End Sub

Hope this helps

Bob Scriver

 

[scriverb]

I guess I should have added that I would import the IP addresses into two seperate fields in your table. Put the first one in IPlow and the second in IPhigh. This way you could loop through all of these records with the above conversion routine to create numbers that could be used in your query. I can help you with that if this is what you want to do. The conversion takes a string and creates another string. These strings would have to be then converted into Long Integer fields for data storage in your table.

The import would bring it in as is (iplow - iphigh). Then convert these in a query to iplow and iphigh.

UPDATE tblIPAddresses SET tblIPAddresses.IPlow = Mid$([tblIPAddresses]![IPrange],1,InStr(1,[tblIPAddresses]![IPrange],&quot; -&quot-1), tblIPAddresses.IPhigh = Mid$([tblIPAddresses]![IPrange],InStr(1,[tblIPAddresses]![IPrange],&quot; - &quot+3);

The above SQL seperates the two IP addresses and updates them in two text(20) fields in the table.

Now the conversion must take place. The conversion will strip the periods(.) and put the string in the right format. Also, convert this final string to a number value and store it in the Long Integer fields. We can adjust the above VBA code to do that in a loop through all of the records if this is how you want to do this.

I will let you take a look at what I have give you and if we are on the right track can update the process to finish the conversion.

Bob Scriver