large number, store and sort

[AHopper]

Two types of numbers.
First type:
I have a 20 digit number that will be scanned into the
database and it can store with or without spaces (I am
presently planning to store it without spaces). The
number will not be used for math purposes but will need
to be sorted on the first seven of the last eight digits
(see below)

Example:
7780 1474 3470 0227 3664
Needs to be sorted on 0227 366

Sorting on these numbers will put the whole number in the
right sequence (ascending or descending) for reports etc.

What field type would be best to use - text, number etc.?

If number is the right choice, what field size - Long
Integer, Single, Double etc.?

How would I set up to sort (first seven of last eight
digits)?

Second Type:
C 108900 P 00516338
This number can be scanned and stored with or without
spaces.

I would like to store only digits greater than 0 from the
00516338, in this case 516338. (Let me know if this is a
question for Forms, Forms programming since I think it
will need to be set up in one of the events of the text
box when the entry is made.

What field type would be best to use?

If number is the right choice, what field size - Long
Integer, Single, Double etc.?

How would I store only digits greater than 0 from the
last eight digits?

Thank you in advance for your help.

Allan

 

[John Nurick]

Hi Allan,

For most purposes it's best to think of this sort of thing as a string
of digits rather than a number: especially as the basic number types
offered by VBA and Jet don't offer 20 digit precision!

If you need to sort on just some of the digits it would seem that this
isn't a single piece of data but actually consists of 3 or more fields
(the first 12, the next 7, and the last 1 which is presumably a
checksum). If so, there's a strong case for storing it in 3 or more
fields. Index the 7-digit field and Bob's your uncle. (Otherwise, you
have to sort on an expression, e.g.
ORDER BY Mid([TheNumber], 13, 7)
).

For the second kind of number, again it's worth storing it in four
pieces (especially if you want to dump the leading zeroes in the last
piece). Using a Number (Long) field here (where there aren't too many
digits) means any leading zeroes are automatically dumped and the field
still sorts correctly.

 

[AHopper]

John,
I understand what you are saying and I think I know one
way I can do this.
Create however many fields in a table I want and make
text boxes on the form for each field. Make one unbound
text box and in the After Update make a declare a
variable for the input number with put code that will
populate each of the entry text boxes with the portion of
the number I want in that table field.
The one thing I am not certain about is index the 7-digit
field. Does that mean make it so it will not accept
duplicates? I am not clear on indexed and non indexed
fields.

Thanks
Allan

-----Original Message-----
Hi Allan,

For most purposes it's best to think of this sort of thing as a string
of digits rather than a number: especially as the basic number types
offered by VBA and Jet don't offer 20 digit precision!

If you need to sort on just some of the digits it would seem that this
isn't a single piece of data but actually consists of 3 or more fields
(the first 12, the next 7, and the last 1 which is presumably a
checksum). If so, there's a strong case for storing it in 3 or more
fields. Index the 7-digit field and Bob's your uncle. (Otherwise, you
have to sort on an expression, e.g.
ORDER BY Mid([TheNumber], 13, 7)
).

For the second kind of number, again it's worth storing it in four
pieces (especially if you want to dump the leading zeroes in the last
piece). Using a Number (Long) field here (where there aren't too many
digits) means any leading zeroes are automatically dumped and the field
still sorts correctly.

Two types of numbers.
First type:
I have a 20 digit number that will be scanned into the
database and it can store with or without spaces (I am
presently planning to store it without spaces). The
number will not be used for math purposes but will need
to be sorted on the first seven of the last eight digits
(see below)

Example:
7780 1474 3470 0227 3664
Needs to be sorted on 0227 366

Sorting on these numbers will put the whole number in the
right sequence (ascending or descending) for reports etc.

What field type would be best to use - text, number etc.?

If number is the right choice, what field size - Long
Integer, Single, Double etc.?

How would I set up to sort (first seven of last eight
digits)?

Second Type:
C 108900 P 00516338
This number can be scanned and stored with or without
spaces.

I would like to store only digits greater than 0 from the
00516338, in this case 516338. (Let me know if this is a
question for Forms, Forms programming since I think it
will need to be set up in one of the events of the text
box when the entry is made.

What field type would be best to use?

If number is the right choice, what field size - Long
Integer, Single, Double etc.?

How would I store only digits greater than 0 from the
last eight digits?

Thank you in advance for your help.

Allan

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

Allan,

You can set an index to either accept or reject duplicate values. You
can also create indexes that include more than one field, so you could
have one index for the entire 3 or 4-field "number" and another for just
the 7 "sortable" digits.

John,
I understand what you are saying and I think I know one
way I can do this.
Create however many fields in a table I want and make
text boxes on the form for each field. Make one unbound
text box and in the After Update make a declare a
variable for the input number with put code that will
populate each of the entry text boxes with the portion of
the number I want in that table field.
The one thing I am not certain about is index the 7-digit
field. Does that mean make it so it will not accept
duplicates? I am not clear on indexed and non indexed
fields.

Thanks
Allan

-----Original Message-----
Hi Allan,

For most purposes it's best to think of this sort of thing as a string
of digits rather than a number: especially as the basic number types
offered by VBA and Jet don't offer 20 digit precision!

If you need to sort on just some of the digits it would seem that this
isn't a single piece of data but actually consists of 3 or more fields
(the first 12, the next 7, and the last 1 which is presumably a
checksum). If so, there's a strong case for storing it in 3 or more
fields. Index the 7-digit field and Bob's your uncle. (Otherwise, you
have to sort on an expression, e.g.
ORDER BY Mid([TheNumber], 13, 7)
).

For the second kind of number, again it's worth storing it in four
pieces (especially if you want to dump the leading zeroes in the last
piece). Using a Number (Long) field here (where there aren't too many
digits) means any leading zeroes are automatically dumped and the field
still sorts correctly.

Two types of numbers.
First type:
I have a 20 digit number that will be scanned into the
database and it can store with or without spaces (I am
presently planning to store it without spaces). The
number will not be used for math purposes but will need
to be sorted on the first seven of the last eight digits
(see below)

Example:
7780 1474 3470 0227 3664
Needs to be sorted on 0227 366

Sorting on these numbers will put the whole number in the
right sequence (ascending or descending) for reports etc.

What field type would be best to use - text, number etc.?

If number is the right choice, what field size - Long
Integer, Single, Double etc.?

How would I set up to sort (first seven of last eight
digits)?

Second Type:
C 108900 P 00516338
This number can be scanned and stored with or without
spaces.

I would like to store only digits greater than 0 from the
00516338, in this case 516338. (Let me know if this is a
question for Forms, Forms programming since I think it
will need to be set up in one of the events of the text
box when the entry is made.

What field type would be best to use?

If number is the right choice, what field size - Long
Integer, Single, Double etc.?

How would I store only digits greater than 0 from the
last eight digits?

Thank you in advance for your help.

Allan

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.