Text to columns

[none]

I have a field in a table where IP addresses are stored. I would like
to build a query where I getfour columns, each housing one of the
octets of the IP address. So, I would have something lilke
IP_Address, A, B, C, D
12.1.123.4, 12, 1, 123, 4

I have been playing around with the InStr function, but haven't gotten
past the first octet.
A: Left([IP_Address],InStr(1,[IP_Address],".")-1)

Any help would be greatly appreciated!

 

[Allen Browne]

Could you design the table so you have 4 fields of type Number, size Byte?

You can then display them on a report with a text box bound to:
=[IP1] & "." & [IP2] & "." & [IP3] & "." & [IP4]

 

[Ken Snell \(MVP\)]

Here are solutions (there are other ways to do this as well):

A: Left([IP_Address],InStr(1,[IP_Address],".")-1)

B: Left(Mid([IP_Address],InStr(1,[IP_Address],".") + 1),
InStr(Mid([IP_Address],InStr(1,[IP_Address],".") + 1), ".") -1))

C: Left(Mid(Mid([IP_Address],InStr([IP_Address],".") + 1),
InStr(Mid([IP_Address],InStr(1,[IP_Address],".") + 1), ".") + 1),
InStr(Mid(Mid([IP_Address],InStr([IP_Address],".") + 1),
InStr(Mid([IP_Address],InStr(1,[IP_Address],".") + 1), ".") + 1), ".") - 1)

D: Mid([IP_Address], InStrRev([IP_Address], ".") + 1)

 

[none]

I am linking to data that is stored elsewhere, and the data is in one
field. I don't need to input new data, I just need to massage the
existing data.

Thanks!

 

[John W. Vinson]

I have a field in a table where IP addresses are stored. I would like
to build a query where I getfour columns, each housing one of the
octets of the IP address. So, I would have something lilke
IP_Address, A, B, C, D
12.1.123.4, 12, 1, 123, 4

I have been playing around with the InStr function, but haven't gotten
past the first octet.
A: Left([IP_Address],InStr(1,[IP_Address],".")-1)

Any help would be greatly appreciated!

Take a look at the Split() function instead:

Split([IP_Address], ".")(0)

will be the first octet, (3) the fourth. You might be able to use this syntax
directly in a query or you may need to write a custom function like

Public Function Octet(strIP As String, iWhich As Integer) As Integer
Dim IP As Integer(3)
IP = Split(strIP, ".")
Octet = IP(iWhich)
End Function

 

[none]

Thanks all for your help. Exactly what I was looking for.