ISBN data type

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
0
Utf
5/4/2007 6:09:03 PM
access 16762 articles. 3 followers. Follow

8 Replies
1338 Views

Similar Articles

[PageSpeed] 52

lcnlit <lcnlit@discussions.microsoft.com> wrote:

>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
0
Tony
5/4/2007 6:15:28 PM
only SQL Server supports custom datatypes

Acceess MDB is ridiculous for anyone to use for any reason




On May 4, 11:09 am, lcnlit <lcn...@discussions.microsoft.com> wrote:
> 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


0
dbahooker
5/4/2007 7:13:39 PM
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





On May 4, 11:15 am, "Tony Toews [MVP]" <tto...@telusplanet.net> wrote:
> lcnlit <lcn...@discussions.microsoft.com> wrote:
> >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 athttp://www.granite.ab.ca/accsmstr.htm


0
dbahooker
5/4/2007 7:14:26 PM
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







On May 4, 12:13 pm, "dbahoo...@hotmail.com" <dbahoo...@hotmail.com>
wrote:
> only SQL Server supports custom datatypes
>
> Acceess MDB is ridiculous for anyone to use for any reason
>
> On May 4, 11:09 am, lcnlit <lcn...@discussions.microsoft.com> wrote:
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -


0
dbahooker
5/4/2007 7:15:20 PM
On Fri, 4 May 2007 11:09:03 -0700, lcnlit <lcnlit@discussions.microsoft.com>
wrote:

>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]
0
John
5/4/2007 9:27:00 PM
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



"lcnlit" <lcnlit@discussions.microsoft.com> wrote in message
news:75F76E6E-78CB-4499-BDA6-DF7816A74C82@microsoft.com...
> 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


0
Aaron
5/4/2007 9:49:56 PM
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






"Aaron Kempf" <akempf@dol.wa.gov> wrote in message
news:uT3dnYpjHHA.3996@TK2MSFTNGP06.phx.gbl...
> 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
>
>
>
> "lcnlit" <lcnlit@discussions.microsoft.com> wrote in message
> news:75F76E6E-78CB-4499-BDA6-DF7816A74C82@microsoft.com...
> > 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
>
>


0
Aaron
5/4/2007 11:26:34 PM
what about size jackass

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




On May 4, 2:27 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Fri, 4 May 2007 11:09:03 -0700, lcnlit <lcn...@discussions.microsoft.com>
> wrote:
>
> >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]


0
dbahooker
5/7/2007 7:35:14 PM
Reply:

Similar Artilces:

Requesting Data from Exchange
Using Outlook XP Pro on a W2K Pro box along with Exchange 5.5sp4. Everytime I open Outlook/mailbox, it takes a long time and a popup appears stating it is requesting data from the exchange server and I have to wait for the retrieving is complete. This only happens on one computer and I have tried several other mailboxes on the same computer with the same results. I have opened the same mailbox on another computer and it works fine so I am suspecting it is Outlook on that particular computer that is causing this. I have run Detect & Repair and still have the same results. N...

Hiding Data #2
I want to have a spread sheet that when printed will have some of the data hidden... I thought the most effective method would be to have two almost identical sheets in a workbook, with the "private" data not copied to the second sheet. This would be similar to an invoice sheet. Is there an easy way I can copy a sheet and then when I paste it to another sheet, it will link the data back to the first sheet? Carl Hi Quick and dirty? On the new sheet, cell A1, type = and then click on A1 in your existing sheet. You can then autofill this across and down the new sheet, and then ...

Source Data & Legends
I have a summary sheet from which a graph is generated, at any time I only need to show half of the items from the summary on the legend but I need to be able to add and remove line items in the graph and the legend but add them in quickly. Is there a method where you can put a tick box and select and de-select line items so they appear and disapear from the legend and the graph without adding and removing source data lines each time. There is a set colour scheme which also has to be adheared to so I cant just take a dump of the data and use a dynamic range graph unless there is a w...

extrapilating data
I am downloading data from my Website from paypal. I would sell items b 1,5 pack, 10 pack ,25 pack, 50 pack and 100 pack. I have my column se up so that I can sort the info by items id and then I can go in an subtotal by count. Problem is that I would like for it to give me m total individual count. I hope that I wrote this write so that you al can interpet it . Ex. 1 pack of 1 nut 1 pack of 5 nuts 1 pack of 10 nuts 1 pack of 100 nuts total nuts sold was 116 nuts. simple example. Can anyone help -- Message posted from http://www.ExcelForum.com I hope I understood your problem correctly so...

summing adjacent data in a worksheet
I have a column of data and I need to sum the values in adjacent cells when they are bounded before and after by cells that contain a value of 0. Then I need to sum the next group of cells bounded by zeros. So I want a column with subtotals rather than one total. The number of cells in one group may vary. I've tried using the sumif function, but what I want doesn't seem to quite fit. I'm using Excel 2007. So, you have something like this: 1 2 3 0 4 5 6 7 0 0 8 9 0 10 11 12 13 0 0 and you want to get something like this: 1 2 3 6 0 ...

Tracking Historic Data
Hi I have a spreadsheet which is updated once a month by several other users, on this spreadsheet i have a summary page which returns me the total number of staff that need training. What I am looking to do is find a way that excel will automatically store what the number of staff that need training is at the beginning of every month. For example I am looking to have a table with January - December with a numerical value next to each. I know this seems like a simple fix by just copying and pasting once a month but ideally i would excel to do it. Is this possible Thanks in ...

Add source data to an existing chart?
Hello! I am building a chart to display, by date, data in columns G (breakfast), D (lunch), and J (dinner). I can get the chart with dates along one axis and column G along the other, but how do I add the data from D and J to create a stacked bar, to show for each day how each of the three columns compare and give a daily total for all three meals? :confused: THANKS TO EVERYONE FOR YOUR HELP! :) -- navychef ------------------------------------------------------------------------ navychef's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29457 View this threa...

"Advanced Windowsby Jeffrey Richter" ISBN: 1572315482 please any one give me the link to downlad for free
Hi, i want to download "Advanced Windowsby Jeffrey Richter" ISBN: 1572315482 please any one give me the link to download this book for free thank you Please can anyone send this book to my gmail account. My account is rameshbabupeddapa...@gmail.com. Why do you think that (a) you are entitled to a free download and (b) such a download exists? We have told you several times that it is unlikely this book can be downloaded online, so why do you persist in asking us to provide you a way to steal it? joe On Tue, 19 Feb 2008 07:41:53 -0800 (PST), rameshbabupeddapalli@gmail.com wrot...

Conditional Formating based on Data Validation
Hi, i am trying to get a row to change colour (using conditional formating) based on the data selected in a cell. The cell is based on data validation. I set up a colum for the data validation (colum H) and used this as the basis for the data validation list. However, i cannot get the row to change different colours based on the option selected (e.g., On Order, Fitted, Query etc) in the row. I can however, get the single cell (e.g., H11, H12 etc) to change differebt colours but not A11 - G11 etc). Please help! I think this might be straight forward but i am missing so...

write a query to retrieve data
I have three sheets in my workbook: Request sheet with request number and date,Fault sheet with request number and faults count,and in the third sheet I have month start date and end date. I want to retrieve data such that : sum of all the faults of those requests which fall in the month given in third sheet Request Sheet Request ID ActualEnd Date 1 1/22/2006 2 2/27/2006 3 1/22/2006 4 1/22/2006 5 1/22/2006 Fault Sheet Request ID Total 1 6.75 2 3.75 3 7.50 third sheet Jan-06 1/1/2006 1/31/2006 to get value Hi, There may be a better way of organizing yo...

Sorting data
Hi, My question here is.. how to sort a set of data in MS CRM system? For example, there is a set of data from "Case" that contain 3 types of category which are 'Problem', 'Request' and 'Question'. My question here,what should i do so that i can sorts the data those data? If it is query/filter data, that will be no problem, but this is about sorting the data. ...

Data Validation, Invalid Data Imput
I'm using data validation for a drop down list in several sheets in a workbook. I have them all accessing a list that is on worksheet one. Access is through defining a name for the list called =salespeople. The problem is, I can't get the Invalid Data Stop message to work. If I access the list without the using the name =salespeople for the source and just use direct cell references it works fine. Is this a glitch or by design? brodiemac Wrote: > I'm using data validation for a drop down list in several sheets in a > workbook. I have them all accessing a list th...

vc++ different basic types
Recently i downloaded a wireless card vc++ open source project from http://wire.cs.nthu.edu.tw/wire1x/wire1x_2_2_src.zip but when i build the project in vc++ 2005 i got 5 errors : 1>d:\sourceforge\wire1x_2_2_src\wpa.h(80) : error C2371: 'NDIS_802_11_KEY' : redefinition; different basic types 1> c:\program files\microsoft visual studio 8\vc\platformsdk\include\ntddndis.h(914) : see declaration of 'NDIS_802_11_KEY' 1>d:\sourceforge\wire1x_2_2_src\cm_func.h(156) : error C2371: 'NDIS_802_11_REMOVE_KEY' : redefinition; different basic types 1> c:\...

Selection of data for plotting graphs
To all, I have a workbook with multiple worksheets labelled data1, data2, data3, and data4. I also have one sheet called 'graph'. On worksheet labelled 'graph' I have a line graph that currently plots the data in sheet 'data1'. however what I want to do is as follows: Add a dropdown menu to sheet 'graph' Use this dropdown menu so that I can select which data is displayed in the graph. i.e. data from 'data1' or data from 'data2' etc. The format of the data i.e. column headings and layout is the same for each worksheet. The length of the data is...

exporting data
What is the best way to export specific data to excel or .csv format? JayK, Create a report that has the info you wish to export, right click on the body of the report, Export | File | Comma Separated CSV and give it a name of the file. -- * "JayK" <JayK@discussions.microsoft.com> wrote in message news:A7B82E63-4B8C-4B68-8A8B-EB36D0F1DCC4@microsoft.com... What is the best way to export specific data to excel or .csv format? From an existing Active Report is very simple. Just run the report and at the top of the report you should see an icon to the left of the one th...

How can I import Act! 2006 data to Outlook 2003?
I want to convert my contacts in Act! 2006 to Outlook 2003 so I can export the contacts from Outlook 2003 to my Nokia phone. Apparently Outlook 2003 can import Act! 2000 but there is no listing for Act! 2006. If I try importing Act! 2000 I cannot find the file type Outlook is looking for. can you convert them to Act2000 format? Another format outlook supports? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://...

How to send data from webservice
Hi, This may be off topic but for sure someone will able to help me. I have a web service that return 1MB data. When I ran the client it took 10 sec to get the data but when I checked the server side it took only 1 sec to build the data. Is there a way I can send the data much faster? Where is this 9 sec overhead coming from? Thanks a lot. CSharper wrote: > Hi, > > This may be off topic but for sure someone will able to help me. I > have a web service that return 1MB data. When I ran the client it took > 10 sec to get the data but when I checked the server side...

graphing data with standard deviaton
after finding the standard deviation of my data how do I graph/plot the data in Excel having +3 standard deviation and -3 standard deviation thank you for any help Hi, You should find the information you need in Jon's section on statistical charts. http://peltiertech.com/Excel/Charts/statscharts.html Cheers Andy patiencescientist wrote: > after finding the standard deviation of my data how do I graph/plot the data > in Excel having +3 standard deviation and -3 standard deviation thank you for > any help -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

ISBN
How can you format cells to show ISBN nus. I can't get the initial 0 to show and it won't read it as an number when the final character is an X. Hi as the ISBN number can contain characters AFAIK there's no chance to apply a custom format for these numbers >-----Original Message----- >How can you format cells to show ISBN nus. I can't get the initial 0 to show and it won't read it as an number when the final character is an X. >. > ...

auto fill a cell with the same data
i want to fill a cell with '-----'. if it is a wide cell or narrower cell,is there a key stroke that you can precede the character '-' with to fill the entire cell instead of entering the character lots of times to fill it? Thanks - Kev. Format / Cells / Alignment Tab / Horizontal / Fill This will fill the cell with whatever character(s) you put in the cell. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- ...

Importing Data & Refresh Data
I have for some time updating a worksheet in Excel 2002 from a CSV file. The source of that file has now switched from comma separated to tab separated. I can't seem to prevent Excel from trying to read the file wrong. I can't even import the tab-separated file into a blank worksheet within the same Excel file. How do I cancel out Excel's inappropriate knowledge of the file so that I can tell it about the tab-separated version? Thank you, George Is the extension of the file still ".csv"? If so, change it to ".txt" and use the Text Import wizard to import...

Timeline based on hourly data
I need to create a time line for hourly data, tide vs location along a coastline. The range is less than 24 hours of data. Selecting time scale as a chart option will setup a time line and arrange event by order of occurance but it seems to ignore time. Is it possible to do this on an hourly basis? Make sure you're using an XY chart. -- David Biddulph "Robert H" <robert.hatcher@l-3com.com> wrote in message news:1188171114.559578.62560@22g2000hsm.googlegroups.com... >I need to create a time line for hourly data, tide vs location along a > coastline. The range i...

3 Different Data Sets
I am trying to create one real estate chart that has the following three components: (1) Bar Chart = Square Footage, (2) Line chart representing a percentage, and (3) Line chart representing dollars. Any idea if this can even be done? Thanks. ...

ISBN problem
Hi guys I'm having a problem with my product catalogue with my online store. download the catalogue to .csv file and open it in excel. When I ope the file it removes the zero from the ISBN number. (eg 07654321 become 7654321). This is becoming a big pain. Is there anyway I can prevent excel from automatically doing thi -- digital21s ----------------------------------------------------------------------- digital21st's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2957 View this thread: http://www.excelforum.com/showthread.php?threadid=49274 Hi Try renam...

CBitmap binary data
Hello, How do i get a pointer to the internal image data of a bitmap using CBitmap? I want to read out the header and the following pixeldata. I have included a bitmap in the resource workshop linked it into the application and want to read out this data from there. In the resource workshop you can display the binary data of a bitmap, thats the data i want to transfer to a buffer. Hope this makes sense, please help. Look at GetDIBits. joe On Fri, 9 May 2008 17:48:22 -0700 (PDT), RAN <nijenhuis@wish.nl> wrote: >Hello, > >How do i get a pointer to the internal image data ...