Which field to use

[Ana_T]

Hi,
I need to store the web visitor's IP in a field. I thought of creating a
numeric field but I've been told that because the IP has numbers and dots
should be stored as text. However, if I choose text I cannot group by it.
What's your opinion?
Of course...newbie here.
TIA
Ana

 

[KARL DEWEY]

No doubt about it, it has to be text to store the data.
This is one of those cases where you next to store duplicate date by adding
a second field and parsing the IP to add leading zeros.
Like this --
IP SortIP
133.12.25.116 133.012.025.116

 

[Brendan Reynolds]

I would use four integer fields and concatenate them when required ...

SELECT [IPA] & "." & [IPB] & "." & [IPC] & "." & [IPD] AS IP
FROM tblTest
ORDER BY tblTest.IPA, tblTest.IPB, tblTest.IPC, tblTest.IPD;

 

[Allen Browne]

Brendan, I agree with your approach of using the 4 number fields.
It's atomic, and handles different types of domains sensibly.

I usually use Byte rather than Integer though.

And I usually add a validation rule to the *table* (not the fields) that
says it's all-or-nothing across the 4, i.e.:
(([IP1] Is Null) And ([IP2] Is Null) And ([IP3] Is Null) And ([IP4] Is
Null))
OR (([IP1] Is Not Null) And ([IP2] Is Not Null) And ([IP3] Is Not Null)
And ([IP4] Is Not Null))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would use four integer fields and concatenate them when required ...

SELECT [IPA] & "." & [IPB] & "." & [IPC] & "." & [IPD] AS IP
FROM tblTest
ORDER BY tblTest.IPA, tblTest.IPB, tblTest.IPC, tblTest.IPD;


--
Brendan Reynolds
Access MVP

Hi,
I need to store the web visitor's IP in a field. I thought of creating a
numeric field but I've been told that because the IP has numbers and dots
should be stored as text. However, if I choose text I cannot group by it.
What's your opinion?
Of course...newbie here.
TIA
Ana

 

[Brendan Reynolds]

I think those are both good ideas, Allen, thanks.

--
Brendan Reynolds
Access MVP

Brendan, I agree with your approach of using the 4 number fields.
It's atomic, and handles different types of domains sensibly.

I usually use Byte rather than Integer though.

And I usually add a validation rule to the *table* (not the fields) that
says it's all-or-nothing across the 4, i.e.:
(([IP1] Is Null) And ([IP2] Is Null) And ([IP3] Is Null) And ([IP4] Is
Null))
OR (([IP1] Is Not Null) And ([IP2] Is Not Null) And ([IP3] Is Not Null)
And ([IP4] Is Not Null))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would use four integer fields and concatenate them when required ...

SELECT [IPA] & "." & [IPB] & "." & [IPC] & "." & [IPD] AS IP
FROM tblTest
ORDER BY tblTest.IPA, tblTest.IPB, tblTest.IPC, tblTest.IPD;


--
Brendan Reynolds
Access MVP

Hi,
I need to store the web visitor's IP in a field. I thought of creating a
numeric field but I've been told that because the IP has numbers and
dots should be stored as text. However, if I choose text I cannot group
by it. What's your opinion?
Of course...newbie here.
TIA
Ana

 

[Allen Browne]

An IP address is a 4-byte numeric value. It is not a string, even if it is
commonly displayed as discrete bytes in decimal format and separated by
dots. And limiting the characters to digits does not yield adequate
validation.

If Access had an unsigned Long Integer, that would be ideal. It doesn't, and
I suspect that treating a signed Long as unsigned would be too confusing for
most Access users. So 4 discrete unsigned bytes is the nearest storage match
and clearest visual representation.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I agree with your approach of using the 4 number fields.
It's atomic, and handles different types of domains sensibly.

So you would take a single atomic fact and split it into parts that
only have meaning when considered as a whole, for which you must
concatenate (generally an expensive operation) and change data type in
the process? Having to convert data every time your retrieve is always
a strong indication that your are storing it wrong.

Why not make it CHAR(15) and make the column/field Validation Rule

[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9]

and probably some other rules beyond the basic pattern.

Jamie.