Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Using number with a decimal as a key number?

Discussion in 'Information Technology' started by Stan McKay, Nov 15, 2005.

  1. Stan McKay

    Stan McKay
    Expand Collapse
    Guest

    I am trying to use the time and billing sample program that comes with Access
    since it is quite close to my needs. However, instead of having an
    autonumber key, I need to use a file number from our existing files which is
    a 5 digit number, with or without a decimal. When I set the number to
    "decimal" it still displays only an integer and will not accept 11111.1 and
    11111.2 as not "duplicate"

    What can I do?
     
  2. Loading...


  3. Ofer

    Ofer
    Expand Collapse
    Guest

    Hi Stan
    It could be that the field type is Long or integer, two types that will
    round the number, so it's like you actually type 1 twice and that gives you
    the duplicate error.
    Try and change the field type to double or single, and then try the decimal
    places
    --
    I hope that helped
    Good Luck


    "Stan McKay" wrote:

    > I am trying to use the time and billing sample program that comes with Access
    > since it is quite close to my needs. However, instead of having an
    > autonumber key, I need to use a file number from our existing files which is
    > a 5 digit number, with or without a decimal. When I set the number to
    > "decimal" it still displays only an integer and will not accept 11111.1 and
    > 11111.2 as not "duplicate"
    >
    > What can I do?
     
  4. SusanV

    SusanV
    Expand Collapse
    Guest

    Hi Stan,

    Setting the field as a double rather than an integer should do the trick.
    --
    hth,
    SusanV

    "Stan McKay" <Stan McKay@discussions.microsoft.com> wrote in message
    news:FCE4F8E4-2105-43F5-A17E-12A9ADF858EF@microsoft.com...
    >I am trying to use the time and billing sample program that comes with
    >Access
    > since it is quite close to my needs. However, instead of having an
    > autonumber key, I need to use a file number from our existing files which
    > is
    > a 5 digit number, with or without a decimal. When I set the number to
    > "decimal" it still displays only an integer and will not accept 11111.1
    > and
    > 11111.2 as not "duplicate"
    >
    > What can I do?
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 14 Nov 2005 10:09:06 -0800, "Stan McKay" <Stan
    McKay@discussions.microsoft.com> wrote:

    >I am trying to use the time and billing sample program that comes with Access
    >since it is quite close to my needs. However, instead of having an
    >autonumber key, I need to use a file number from our existing files which is
    >a 5 digit number, with or without a decimal. When I set the number to
    >"decimal" it still displays only an integer and will not accept 11111.1 and
    >11111.2 as not "duplicate"
    >
    >What can I do?


    I don't usually disagree with Ofer or Susan, but in this case I must
    make a warning. Double and Single Float numbers are risky as keys,
    since they are *approximations*. What's stored might not in fact be
    11111.1 - it might be 11111.099999997 one time, and 11111.1000000002
    another, depending on how it gets entered.

    I would suggest either using a Currency datatype - which allows up to
    four decimals; or if you have Access2002 or later, a Decimal datatype
    where you can specify the decimals. The default number Long Integer
    won't work.

    Analternative(since the file number will never be used for
    calculations) would be to use a Text field with an input mask 00000.0
    to force entry of digits.

    John W. Vinson[MVP]
     
  6. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    John Vinson wrote:
    > I don't usually disagree with Ofer or Susan, but in this case I must
    > make a warning. Double and Single Float numbers are risky as keys,
    > since they are *approximations*.
    >
    > (since the file number will never be used for
    > calculations) would be to use a Text field


    I agree with JohnV: this sounds like text.

    > with an input mask 00000.0
    > to force entry of digits.


    I disagree with JohnV: I don't think an input mask would prevent bad
    data from being entered into the database. Instead, use a pattern
    matching CHECK constraint a.k.a. Validation Rule e.g.

    CREATE TABLE MyTable (
    key_col VARCHAR(7) NOT NULL UNIQUE,
    CHECK (
    key_col LIKE '[1-9][0-9][0-9][0-9][0-9].[1-9]'
    OR key_col LIKE '[1-9][0-9][0-9][0-9][0-9]'
    ),
    (other columns ...)
     
  7. SusanV

    SusanV
    Expand Collapse
    Guest

    Thanks for the heads up John - I wasn't aware of the discrepancy in what's
    actually stored!

    Susan

    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:ipkin19cgjgun4hefugpdaf36d3hrr8nm5@4ax.com...
    > On Mon, 14 Nov 2005 10:09:06 -0800, "Stan McKay" <Stan
    > McKay@discussions.microsoft.com> wrote:
    >
    >>I am trying to use the time and billing sample program that comes with
    >>Access
    >>since it is quite close to my needs. However, instead of having an
    >>autonumber key, I need to use a file number from our existing files which
    >>is
    >>a 5 digit number, with or without a decimal. When I set the number to
    >>"decimal" it still displays only an integer and will not accept 11111.1
    >>and
    >>11111.2 as not "duplicate"
    >>
    >>What can I do?

    >
    > I don't usually disagree with Ofer or Susan, but in this case I must
    > make a warning. Double and Single Float numbers are risky as keys,
    > since they are *approximations*. What's stored might not in fact be
    > 11111.1 - it might be 11111.099999997 one time, and 11111.1000000002
    > another, depending on how it gets entered.
    >
    > I would suggest either using a Currency datatype - which allows up to
    > four decimals; or if you have Access2002 or later, a Decimal datatype
    > where you can specify the decimals. The default number Long Integer
    > won't work.
    >
    > Analternative(since the file number will never be used for
    > calculations) would be to use a Text field with an input mask 00000.0
    > to force entry of digits.
    >
    > John W. Vinson[MVP]
     

Share This Page