Hi,

   Here's an interesting question:

   I have a table that looks like this:

tblSubNet
   PK IPID
   IPAddress
   Location
   Status

   I have another table that has among other things, the following:

tblComputer
   PK PCID
   ComputerName
   UserName
   IPAddress

   On a case by case basis, I want to look up the IPAddress of a computer from the tblComputer table (XXX.XXX.XXX.XXX), look for the first three octets in the tblSubNet.IPAddress (XXX.XXX.XXX), and based on a comparison, find the Location of the ComputerName.

   Example data:

tblSubNet
   PKID: 1
   IPAddress: 192.168.0
   Location: Home
   Status: Home User

tblComputer
   PCID: 1
   ComputerName: MyComputer
   UserName: mmcdonal
   IPAddress: 192.168.0.100

   Based on the location of the subnet in the first table, I want to determine (in a report) the location of the computer called MyComputer.

   I am thinking I will need a Module that looks through tblComputer where the PCID on the report line matches the PCID in the table, then reads the IPAddress, uses an array to break it into its constituent parts, then looks up the first octet in tblSubNet, then based on that filter, looks up the second octet, then based on that filter, looks up the third octet, and returns the Location based on the final match. The Subnets are unique (so an autonumber PK isn't really needed.)

   Here's another kicker: This is an Access .mdb file, and the tables are all linked to a SQL server.

   Yee-Ha!

mmcdonal

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

This is what I'm doing
SELECT Location
FROM dbo_tblSubNet where IPAddress in
(SELECT left(IPAddress,9) from dbo_tblComputer where IPAddress Like '192.168.0*' and ComputerName = 'MyComputer')