MS Access Db

[shyler82]

I have an IP Address table which consists of pool of addresses with
lots of other fields. I am trying to make IP Address field a Primary
key but when i make it a primary key it sorts the IP Address like this
10.0.1.0, 10.0.1.1, 10.0.1.10, 10.0.1.100 and so on automatically . I
dont want like this in my table as it should be in numeric order like
1,2,3,4 ,5 etc so anybody can tell me how can i make it in a numeric
order while having the ip address field as a primary key..


Regards,
Khizer

 

[BruceM]

What order do you want? You say you want it in order "like 1, 2, 3, 4, 5",
but where are these numbers? Your example shows only ones and zeros, and
looks to be sorting as it should.
That the field is a PK is irrelevant to how the data are sorted. Another
point here is that tables are just storage. To sort, use a query (for a
report you can use the report's Sorting capabilities).
If you are storing IP addresses as you have written them you must be using a
text field. Is that correct? If so, text sort order won't necessarily
produce the same results as numeric sort order.

 

[shyler82]

What order do you want?  You say you want it in order "like 1, 2, 3, 4, 5",
but where are these numbers?  Your example shows only ones and zeros, and
looks to be sorting as it should.
That the field is a PK is irrelevant to how the data are sorted.  Another
point here is that tables are just storage.  To sort, use a query (for a
report you can use the report's Sorting capabilities).
If you are storing IP addresses as you have written them you must be usinga
text field.  Is that correct?  If so, text sort order won't necessarily
produce the same results as numeric sort order.







- Show quoted text -

The IP Address Tables consist of range of IP Addresses like
192.168.55.0-192.168.55.255, 10.0.1.0-10.0.1.255 and so on .. They are
stored in text format so when i make this field as a PK it just sort
it and mess the ip address ranges.Actually we need to see ip in the
ranges like from 0-255 not like 10.0.1.0, 10.0.1.1, 10.0.1.10,
10.0.1.100 and so on. How can i achieve this? Do i need to wirte any
macro or module for it?

 

[Douglas J. Steele]

The easiest way would be to store the IP in four separate numeric fields.
You can always concatenate them back together via a calculated field in a
query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

What order do you want? You say you want it in order "like 1, 2, 3, 4, 5",
but where are these numbers? Your example shows only ones and zeros, and
looks to be sorting as it should.
That the field is a PK is irrelevant to how the data are sorted. Another
point here is that tables are just storage. To sort, use a query (for a
report you can use the report's Sorting capabilities).
If you are storing IP addresses as you have written them you must be using
a
text field. Is that correct? If so, text sort order won't necessarily
produce the same results as numeric sort order.







- Show quoted text -

The IP Address Tables consist of range of IP Addresses like
192.168.55.0-192.168.55.255, 10.0.1.0-10.0.1.255 and so on .. They are
stored in text format so when i make this field as a PK it just sort
it and mess the ip address ranges.Actually we need to see ip in the
ranges like from 0-255 not like 10.0.1.0, 10.0.1.1, 10.0.1.10,
10.0.1.100 and so on. How can i achieve this? Do i need to wirte any
macro or module for it?

 

[Larry Linson]

They are being sorted properly for numbers in a text field. And for a code
such as IP address, text is the best choice (101.010.001.002 will be
rejected as a number, you realize, because numbers do not have three decimal
points), unless you follow Doug's advice and store the numbers separately.
To sort as you want, you'll need to sort on the numbers, but concatenate
formatted numbers to properly display what you want.

Yes, yes, I know the European / International convention, but that's covered
by Regional Settings.

Larry Linson

What order do you want? You say you want it in order "like 1, 2, 3, 4, 5",
but where are these numbers? Your example shows only ones and zeros, and
looks to be sorting as it should.
That the field is a PK is irrelevant to how the data are sorted. Another
point here is that tables are just storage. To sort, use a query (for a
report you can use the report's Sorting capabilities).
If you are storing IP addresses as you have written them you must be using
a
text field. Is that correct? If so, text sort order won't necessarily
produce the same results as numeric sort order.







- Show quoted text -

The IP Address Tables consist of range of IP Addresses like
192.168.55.0-192.168.55.255, 10.0.1.0-10.0.1.255 and so on .. They are
stored in text format so when i make this field as a PK it just sort
it and mess the ip address ranges.Actually we need to see ip in the
ranges like from 0-255 not like 10.0.1.0, 10.0.1.1, 10.0.1.10,
10.0.1.100 and so on. How can i achieve this? Do i need to wirte any
macro or module for it?

 

[BruceM]

Do you want to store 192.168.55.0 - 192.168.55.255 as 256 separate records,
or as a range in one record? If the former, storing the IP address as four
separate fields, as suggested, is your best choice. Otherwise you would
need to use text functions such as InStr, InStrRev, Len, Right, and Left to
parse the field for sorting.
Again, that the field is the PK is irrelevant. It could be that another
field determined the sort order in the table, then you made the IP address
field the PK and it sorted by that instead, but again you should not be
using tables to determine the order of records.

What order do you want? You say you want it in order "like 1, 2, 3, 4, 5",
but where are these numbers? Your example shows only ones and zeros, and
looks to be sorting as it should.
That the field is a PK is irrelevant to how the data are sorted. Another
point here is that tables are just storage. To sort, use a query (for a
report you can use the report's Sorting capabilities).
If you are storing IP addresses as you have written them you must be using
a
text field. Is that correct? If so, text sort order won't necessarily
produce the same results as numeric sort order.







- Show quoted text -

The IP Address Tables consist of range of IP Addresses like
192.168.55.0-192.168.55.255, 10.0.1.0-10.0.1.255 and so on .. They are
stored in text format so when i make this field as a PK it just sort
it and mess the ip address ranges.Actually we need to see ip in the
ranges like from 0-255 not like 10.0.1.0, 10.0.1.1, 10.0.1.10,
10.0.1.100 and so on. How can i achieve this? Do i need to wirte any
macro or module for it?