Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Which number type to use? (Access 97, so SR)

    Hey All,

    I'm still working on my magazine collection.

    My quandry is this;

    If I set my Issue Numbers to text, and then sort, I get results like: 1,11,12,2,3,31,32. I need to be able to put alpha characters and punctuation in some issue numbers, that's why I chose text originally.

    But, my list is obviously not sorted properly...

    If I set my Issue Numbers to numeric, I get a proper sorting, but then I can't enter my alpha and punctuation.

    Can anyone give some advice on which road to take, and how to work around either problem?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Which number type to use? (Access 97, so SR)

    In a query, use the following expression
    <pre>SortOrder : Space(3 - Len([IssueNumber]) & [IssueNumber]</pre>

    Replace the 3 with the maximum length of your IssueNumber field.
    This will sort the numbers correctly but if you have
    10
    11
    10A
    the 10A will be sorted after the 11.
    In this case I suggest you to use two fields, one for the numbers, and one for the Alpha characters.
    Francois

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Which number type to use? (Access 97, so SR)

    What rules can you rely on as to what an issue "number" can look like? That is, does it ALWAYS start with a number? Based on what you have shown so far, I'd use a 2-field approach; that is an IssueNumber (being numeric) and IssueSuffix (text), in which they together form the PK.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Which number type to use? (Access 97, so SR)

    Wulfgar
    If you use two fields you can amalgamate both fields into one to show the number as you want it (IssueNo =Field1 & Field2). If you can automate the entry to the two fields in some way you can then hide them and of course you sort on Field1 assuming this is the one with the number.
    Peter

  5. #5
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Which number type to use? (Access 97, so SR)

    Folx,

    Here's a sample list of my data.
    Preview
    Prelude
    Prequel
    0
    1
    2
    3
    4
    43/2
    49/4
    Alpha
    Omega
    1-Alpha
    0-Omega

    I know this is a rather eclectic list, but it is accurate and representational.

    With some of the entries, the 2 field idea would work. But it may not work with the the "Alpha" issues for instance.

    Though, on the other hand, [img]/forums/images/smilies/smile.gif[/img] I suppose if I had 2 fields, and left the first one blank, and the second was a text field containing Alpha, it may work.

    But how do I compare 43/4 with Alpha?

    There will be a Prelude, 1,2,3,50,51,52, Omega for instance, all with the same title and publisher.

    I guess the 2 field system would probably be the best path to go. Anyone else have any more thoughts?

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Which number type to use? (Access 97, so SR)

    I think the 2 field approach is about all you can do. I'd make both text fields, however, setting their "Required" property to Yes and "Allow Zero Length String" to Yes. I would right-justify the first field, and try to only put numbers in it. For "Prelude", etc, I'd make the 1st field blank and put "Prelude" in the 2nd field.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    New Lounger
    Join Date
    May 2002
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Which number type to use? (Access 97, so SR)

    You can order your data with Val(Field), (Field). Then your data: 1a,11b, 12a, 2, 20, 21, etc. will sort in the expected 1a, 2, 11b, 12a, 20, 21 order. Look up "Val" in help. It extracts the numeric part of the field up to the first non-numeric character.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •