Re: large number, store and sort

From: John Nurick (j.mapSoN.nurick_at_dial.pipex.com)
Date: 08/19/04 

Date: Thu, 19 Aug 2004 22:34:30 +0100

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.

Relevant Pages

  • RE: Smart four digit sorting procedure
    ... The old code you gave me could sort the four digits independently of where ... Perfoprm the data sort descending instead of ascending ... For RowCount = 1 To LastRow ... If IsNumeric(Mid(MyStr, CharCount, 4)) Then ...
    (microsoft.public.excel.programming)
  • RE: Smart four digit sorting procedure
    ... ThisChar = Mid(MyStr, CharCount, 1) ... "Nic Daniels" wrote: ... The old code you gave me could sort the four digits independently of where ...
    (microsoft.public.excel.programming)
  • Re: large number, store and sort
    ... >of digits rather than a number: ... >If you need to sort on just some of the digits it would ... >digits) means any leading zeroes are automatically ... >>presently planning to store it without spaces). ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Sorting Question
    ... digits in order, then 3 digits in order, then 4 digits in order, etc all ... the sort should deal with strings that begin with Alphas. ... the Alpha letters first and then the numbers following the alpha, ... the Sorting and Grouping area. ...
    (microsoft.public.access.reports)
  • RE: Sort by column problems
    ... Now you can combine and sort. ... contains the entire reciept number which is 9 digits. ... i.e. a particular transaction will be on line 264 on one worksheet but ... transaction and see if the total matches from one set of data to ...
    (microsoft.public.excel.worksheet.functions)