Which field to use

  • Thread starter Thread starter Ana_T
  • Start date Start date
A

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
 
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
 
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, 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.

Brendan Reynolds said:
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


Ana_T said:
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
Click to expand...
Click to expand...
 
I think those are both good ideas, Allen, thanks.

--
Brendan Reynolds
Access MVP

Allen Browne said:
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.

Brendan Reynolds said:
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


Ana_T said:
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
Click to expand...
Click to expand...
Click to expand...
 
I agree with your approach of using the 4 number fields.
It's atomic, and handles different types of domains sensibly.
Click to expand...

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.

--
 
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.

Jamie Collins said:
I agree with your approach of using the 4 number fields.
It's atomic, and handles different types of domains sensibly.
Click to expand...

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.
Click to expand...
 
Allen said:
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.
Click to expand...

I stand corrected :)

I've only ever used an IP address as a text item, therefore it made
sense to me it store it how it was 'used'. Of course, I wouldn't store
a DATETIME value as text, though...

Thanks,
Jamie.

--
 
Back
Top