Using VBA to define query fields???

mmcdonal · November 24th, 2004, 03:45 PM

Hi,

   I am still trying to resolve my IP address issue.

   I have a table with computer information, including the computer's IP Address.

   I have another table with subnets, and the location of the subnets.

   I need a regular expression, or some way to use VBA to define a field in one query to show the subnet of the computer's IP address by parsing the IP Address.

   As an example, this code works in VBScript:

'==============================================
strIP = "192.168.0.100"

NewArray = Split(strIP, ".")
strOct01 = NewArray(0)
strOct02 = NewArray(1)
strOct03 = NewArray(2)

strSubnet = strOct01 & "." & strOct02 & "." & strOct03

WScript.Echo strSubnet
'===============================================
This breaks the IP Addresss into its octets, and then concatenates them with periods to display the subnet (192.168.0)

I need to run a query that looks like this...

'===============================================
SELECT tblComputer.ComputerName, tblComputer.UserName, tblComputer.DHCP
FROM dbo_tblComputer;
'===============================================

I had used "Subnet:(Left([DHCP], 9))" to create a subnet, but some subnets are 10 or more characters. So I didn't think I could use regular expressions.

Once I have the subnet I will compare it to the subnet location table, but I need to get the value to come back as "192.168.0" or "192.16.0" or "192.168.104" etc consistently.

So the issue is a regular expression that will parse this, or a way to use VBA to populate a query field.

THANKS!

mmcdonal