ISBN data type

[lcnlit]

Hi--I am setting up a book lending database for our small non-profit. ISBN's
(identification numbers unique to every book) recently went to 13 digits. I
want to use the book's ISBN as the primary key for tblBooks, but it is too
long for the Long Integer data type, and when you choose Single or Double as
the type, it displays the ISBN with an exponent, i.e. 9781564205223 is
displayed as
9.781565E+12. Is there any way to "force" Access to display the number as
written?

Thanks,
lcnlit

 

[Wayne-I-M]

Hi

ISBN's should be saved as text

 

[Tony Toews [MVP]]

Hi--I am setting up a book lending database for our small non-profit. ISBN's
(identification numbers unique to every book) recently went to 13 digits. I
want to use the book's ISBN as the primary key for tblBooks, but it is too
long for the Long Integer data type, and when you choose Single or Double as
the type, it displays the ISBN with an exponent, i.e. 9781564205223 is
displayed as
9.781565E+12. Is there any way to "force" Access to display the number as
written?

You need to change that field to text. Even a double is too small for
13 digits.

From A2000 help Single Stores numbers from
–3.402823E38 to
–1.401298E–45 for negative values and from
1.401298E–45 to 3.402823E38 for positive values. 7 4 bytes
Double Stores numbers from
–1.79769313486231E308 to
–4.94065645841247E–324 for negative values and from
1.79769313486231E308 to 4.94065645841247E–324 for positive values.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[dbahooker]

only SQL Server supports custom datatypes

Acceess MDB is ridiculous for anyone to use for any reason

 

[dbahooker]

you could use a BIG INTEGER
I think that big integer supports to 300 digits or something insane

of course, that would require Access Data Projects

 

[dbahooker]

and for the record, using BIGINT will _ALWAYS_ be faster than using
Text

using text will consume 26 bytes in Access, 13 as a varchar
as a bigint, it will consume 8 bytes I beleive

 

[John W. Vinson]

Is there any way to "force" Access to display the number as
written?

Use a Text datatype. You'll never need to calculate the sum, or average, or
product of ISBN values...

John W. Vinson [MVP]

 

[Aaron Kempf]

I disagree

if it's all numeric-- then you should use the BIGINT datatype in SQL Server

SQL Server allows for custom datatypes-- MDB does not

in Access, a text 13 field will consume 26 bytes.
In SQL Server, a BIGINT field will only consume 8 bytes

 

[Aaron Kempf]

for example; I just used a custom datatype and a rule to enforce that my
field 'Casenumber' will look like this

CREATE RULE CaseNumber as @Value LIKE
'[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9]___'

this is EASY, it is entirely scalable, it's reusable

The bottom line is that certain 'data quality' tools are _NOT_ practical on
the Access side of the equation (because you need to copy and paste this
validation formula into 100 different places to use it)

SQL Server has a future.
If you care about your data then use a server.

If you don't care about your data then keep it in Excel

furthermore, SQL Server has REAL referential integrity..
AND triggers

so if Rules and Custom DataTypes don't work-- then it's easy to build a
trigger for example

 

[lcnlit]

Thank you to everyone who replied. I will change the data type to "text".

lcnlit

 

[dbahooker]

what about size jackass

use a bigint, it's smaller faster and better integrity for your data