Search IP range query by IP address, URGENT!!!

[Serg_R]

Hi people,
need your help please,

I have a table with these fields:
"FirstIP", "LastIP" and "siteID".
I am trying to run a simple SELECT query in Access to retreive the IP range by supplying a valid IP address. The query, as you see, will propmt for IP address.

Here is the syntax:

SELECT Trim([Enter IP here]) AS Searched_IP, sites_ipranges.FirstIP, sites_ipranges.LastIP, sites_ipranges.SiteID,
FROM sites_ipranges
WHERE (((Trim([Enter IP here])) Between [sites_ipranges]![FirstIP] And [sites_ipranges]![LastIP]))

Results:
Searched_IP | FirstIP | LastIP | SiteID
=============================

What happens, that the query does not return IP ranges for all IPs, even it has a valid IP range.
For instanse, I have tested it with 6 IPs so far and it returns only IP ranges for only four IPs. If I take those two IPs and look up the IP range manually it is there, in the table.
My questions is: Why the query does not return the IP range, just blank fields, even the IP range is there for that IP.
You know, IP range could run from 1 to 255.
What I have noticed, that if an IP's last octet begins with a number more than 2 than it returns blank.
Please help to figure out the solutions and pls ask quetions.
Thank you very much!!!

Sergei

 

[WayneRyan]

Sergei,

Would you believe that 4.10.1.155 is greater than 10.10.1.155?

It is, because the software is treating your IP addresses as strings. No
valid NUMBERS have that many decimal points, so it's treated as a string.

What you need to do is write a function to convert your IPs into something
like: 0004.0010.0001.0155 which is less than 0010.0010.0001.0155. They
don't have to be displayed like that, just used for sorting and selecting.

The function can be used in your queries.

Ever written any VBA code?

Wayne

 

[WayneRyan]

Sergei,

Put this function in a Public Module (Module1 is OK).

Put a new field in your query:

NewIP: ConvertIP([IPNumber])

Where [IPNumber] is one of your table's IP Numbers.

No error checking, but who needs it anyway ?!?

Public Function ConvertIP(IPNumber As String) As String
Dim Quads As Variant
Dim Temp As String
Dim i As Integer

Temp = ""
Quads = Split(IPNumber, ".")
For i = 0 to 3
    Temp = Temp & Format(Quads(i), "000#")
    If i < 3 Then Temp = Temp & "."
    Next i
ConvertIP = Temp
End Function

Wayne

 

[Serg_R]

Thank you Wayne

Thank you Wayne!!!

It works for the IPs that did not return the range previuosly,
Thank you again!!!

I have written VERY simple VBA codes in the past, long ago or modified existing ones. But now you have motivated me to learn VBA. What book would you recommend for me to start with?
Will be verygratefull for your advice,

Sergei

 

[WayneRyan]

Sergei,

You can use the Search Facility here and look for "books", and with
a little scanning find some threads.

Also, look at the coding sample forum and the sample database forum
here.

Wayne