A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

large number, store and sort



 
 
Thread Tools Display Modes
  #1  
Old August 19th, 2004, 07:38 PM
AHopper
external usenet poster
 
Posts: n/a
Default large number, store and sort

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
  #2  
Old August 19th, 2004, 10:34 PM
John Nurick
external usenet poster
 
Posts: n/a
Default

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.


On Thu, 19 Aug 2004 11:38:55 -0700, "AHopper"
wrote:

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.
  #3  
Old August 19th, 2004, 11:35 PM
AHopper
external usenet poster
 
Posts: n/a
Default

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.


On Thu, 19 Aug 2004 11:38:55 -0700, "AHopper"
wrote:

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

  #4  
Old August 20th, 2004, 08:17 AM
John Nurick
external usenet poster
 
Posts: n/a
Default

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.

On Thu, 19 Aug 2004 15:35:22 -0700, "AHopper"
wrote:

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.


On Thu, 19 Aug 2004 11:38:55 -0700, "AHopper"
wrote:

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 [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
auto filter / sort Bernie Deitrick Worksheet Functions 0 June 1st, 2004 01:04 PM


All times are GMT +1. The time now is 12:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.