Using number with a decimal as a key number?

[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?

 

[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

 

[SusanV]

Hi Stan,

Setting the field as a double rather than an integer should do the trick.

 

[John Vinson]

On Mon, 14 Nov 2005 10:09:06 -0800, "Stan McKay" <Stan

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]

 

[peregenem]

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 ...)

 

[SusanV]

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

Susan