Join of IP Addresses not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If this has been answered already, or is better answered somewhere else, then
please let me know.

I have done many joins in Access before - to databases in Access, on SQL
server and to Excel spreadsheets. However, I have hit a problem that I don't
understand. I have a DB in Access that has a table with many fields of
information about servers and network equipment. IP address is the Primary
Key. I am attempting join this table to another table that I have in Access
that includes MAC address and IP Address only - it also has IP address as the
Primary Key. When I join the tables on the IP Address field, I get no matches
at all. So, the Mac Address table looks like this:

10.1.1.1 00-04-23-a6-a0-de
10.1.1.2 00-c0-9f-7a-15-c4

The table is designed with both fields as text fields.

Thanks,
 
Thanks.

I am not actually attempting to match the fields that have the dashes in
them. The join is on the IP Address field that looks like 10.1.1.1 or
10.1.1.243 or 10.1.1.89, etc. Should this be working?


--
Matthew Tisdel
South Carolina


Allen Browne said:
Microsoft mutilated Access from version 2000 onwards so that it does not
match fields reliably if there is a dash in the field if you use the Like
operator.

More info:
Query Returns no Records with an Indexed Field That Contains Dashes
at:
http://support.microsoft.com/kb/271661/en-us
Click to expand...
 
It should provided the fields are identical, i.e. no leading zeros or
spaces.

If you had control over the database structure, you might consider storing
IP addresses in 4 fields of type Byte.
 
Hi,


You want a match if the first three octets are equal?


SELECT a.*, b.*
FROM a INNER JOIN b
ON Left(a.ip, InStrRev( a.ip, ".")) = Left( b.ip, InStrRev( b.ip,
"." ))



Hoping it may help,
Vanderghast, Access MVP


Matthew Tisdel said:
Thanks.

I am not actually attempting to match the fields that have the dashes in
them. The join is on the IP Address field that looks like 10.1.1.1 or
10.1.1.243 or 10.1.1.89, etc. Should this be working?
Click to expand...
 
Thanks for the help.

Actually, I need all 4 octets to match for the join. I have the IP addresses
in both tables, however, one of the tables have MAC Addresses and I need to
put those MAC Addresses in the other table.
 
Thanks. I got it to work with the help of a developer friend.

The MAC Address table had the IP Addresses padded with space. I had to use


New Field Name : Trim([Field Name])

in the query.

Thanks,
 
Back
Top