MS Access 2002: Data type for fractions?

Discussion in 'Databases' started by lespaul00, Nov 22, 2007.

0
  1. #1
    Hello,

    I have a data type in my database as "text" because my values must be fractions. However, when I use the ORDER feature in my queries, it doesn't sort properly because of this.

    For example.... here is a list of "numbers" i have in my TEXT data type:

    1
    3
    3 1/2
    1 1/2
    1/2
    3/4


    When I execute my query, with the ORDER DESC feature, it will display as such:

    1
    1/2
    1 1/2
    3
    3 1/2
    3/4

    rather than:

    1/2
    3/4
    1
    1 1/2
    3
    3 1/2

    I know I can just use decimals with a "number" data type, but I specifically need fractions.

    Suggestions for a solution?
     
    lespaul00, Nov 22, 2007 IP
  2. Kuldeep1952

    Kuldeep1952 Active Member

    Messages:
    290
    Likes Received:
    18
    Best Answers:
    0
    Trophy Points:
    60
    #2
    A possible solution is to use 4 fields:
    - wholeNumber
    - Numerator
    - Denominator
    - RealNumber
    Store the calculated value in Realnumber and use it for Sorting
    and the others for display.
     
    Kuldeep1952, Nov 22, 2007 IP
  3. LincolnAve

    LincolnAve Peon

    Messages:
    213
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Just use a VBA routine to evaluate and sort. Then you can store them in an array.

    LMK
     
    LincolnAve, Nov 28, 2007 IP