Re: large number, store and sort
From: AHopper (anonymous_at_discussions.microsoft.com)
Date: 08/19/04
- Next message: iam_at_notarealisp.com: "Date Fields Question"
- Previous message: Tim Ferguson: "Re: Set Primairykey to ID (also autonumber)."
- In reply to: John Nurick: "Re: large number, store and sort"
- Next in thread: John Nurick: "Re: large number, store and sort"
- Reply: John Nurick: "Re: large number, store and sort"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 19 Aug 2004 15:35:22 -0700
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"
><anonymous@discussions.microsoft.com> 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.
>.
>
- Next message: iam_at_notarealisp.com: "Date Fields Question"
- Previous message: Tim Ferguson: "Re: Set Primairykey to ID (also autonumber)."
- In reply to: John Nurick: "Re: large number, store and sort"
- Next in thread: John Nurick: "Re: large number, store and sort"
- Reply: John Nurick: "Re: large number, store and sort"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
