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
1628 Views

Similar Articles

[PageSpeed] 36

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:

Data Validation enter values with VBA
I currently have a Spreadsheet running a tipping competition which works well, however would like to add additional functionality to it. I have cells setup which are data validated to a list on the sheet and have four values to select from which are None, Team 1, Draw, Team 2 These come up in a drop down box. What I would like to be able to do is to have the data filled automatically in a group of cells dependent on the value in another cell. For example In Cell A10 i put in H and Cell F10 which has the data validated list always has the value None to start with the l...

data entries for 11 months ago for the entire month
Hi. im bit stuck. i need a formula for access whereby it will return all date entries put in 11 months ago. e.g. today is 05/2007 it will return all the entries put in for 11 months previous for the entire month which would be all entries for june. Please help. Field: EntryDate Criteria: Between DateSerial(Year(Date()), Month(Date())-11,1) and DateSerial(Year(Date()), Month(Date())-10,0) If that is off adjust the -11 and -10 by one each to change the number of months in the past. Explanation of the 0 day. The zero day of any month is one day prior to the 1st day of the month. The day ...

Field Type Change
I am trying to change a free text field to a drop down menu in my current database, that already has data entered for this particular field, in order to avoid multiple versions of the same organization that are just typed in differently. I'm assuming that I'll need to recode the different versions of an entry to make them uniform but after that, I'm unsure how to proceed so I won't lose any data. Is this possible or will I need to delete the field, recreate it as a drop down and then re-enter the data? Thanks in advance! Mike Thanks everyone for all the input and advice s...

Saving Input of Userform as a new line of data on another file?
Hi, Wonder if anyone can help me? I have create a form, and want to b able to save the info to another file(A) after I have click on button. Upon my next click, the new info from my form will be save as new entry (the next line) in file A. Thank you -- peppe ----------------------------------------------------------------------- pepper's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1635 View this thread: http://www.excelforum.com/showthread.php?threadid=27737 hi, it might be better to 1 button the whole thing. put the new record on the next line then copy...

Pop up message asks if I really want to edit the live office data
I have macros running in excel which massage data created by Live Office (Business Objects Queries.) With most popup messages I just use the displayalerts statement to get around it. That does not work for this message which specifically addresses the Live Office edits. Does anyone know how to suppress it. Err, what's the message and how does it come about? -- Regards Dave Hawley www.ozgrid.com "Sarah" <Sarah@discussions.microsoft.com> wrote in message news:DF07E5AF-54BD-4809-925A-EA4DEB764070@microsoft.com... >I have macros running in excel whi...

Importing data question
I have a text document that contains unique job numbers, location of th job, date and time last scanned, number of breakages, days in process customer number etc. Several of the fields change multiple times durin a day but job number is unique and would dissappear only once a job i completed. I can figure out how to get external data into an exce spreadsheet and how to refresh the data but my problem is I want to b able to add comments/notes to each one (customer was called regardin delay/part on back order/called customer on xx/xx/xx etc.) Each time refresh data my notes are in the row but ...

Subform not allowing data entry
Dear All I have a control on my main form, Status. When it is set to "C" the subform becomes not visible. When I change the status control to "A" the subform becomes visible but I cannot enter any data. If I move away from this record and then come back to it, I can then enter data into the subform. I would like, obviously, to be able to enter data as soon as it becomes visible. Any suggestions. Thanks Nigel - What is the code behind the Status control? Are you setting the subform properties there (e.g. making the subform visible or not)? Maybe you ...

Trying to Group Data
I am trying to group data. I have a daily record that contains multiple projects and multiple items in the project. I would like to roll the daily data into project 1 and all the items that go with it and then project 2 and all the items that go with it. I have a start but am unsure of where to go. I am attaching the files that I have. Any help would be appreciated. Thanks, Dave ==================== <?xml version="1.0" encoding="utf-8" ?> <?xml-stylesheet type="text/xsl" href="StatusReportProject.xsl" ?> <TimeSheet> <Daily...

lost data....lost person! #2
I'll try to keep it simple....I have Windows XP Home Edition...and The Office 2000 suite....I was useing the Excel 2000 program and forgot to save the data, however, I DID NOT close the program. The data I had put in was gone when I went back to do some more entries and save and close the workbook. When I tried undo in the edit menu it said that I Couldn't undo and only after switching from sheet to sheet did it finally show that I could do one undo but it was on a seperate sheet than the one that I had showing when the option was available and I believe that it was informat...

for some reason when i deploy my C# application on windows 2003 occasionaly rdr[0].ToString() hangs where rdr is a System.Data.SqlClient.SqlDataReader. Is there a fix for this? is my visual studio.net
for some reason when i deploy my C# application on windows 2003 occasionaly rdr[0].ToString() hangs where rdr is a System.Data.SqlClient.SqlDataReader. Is there a fix for this? is my visual studio.net out dated? Hi, 1. Does this seem to you to be the right Newsgroup to ask this question ? I would have suggested "microsoft.public.dotnet.framework.adonet". 2. Is there supposed to be a difference between the Message Subject and the Message body ? Isn't the Message Subject supposed to be abbreviated ? 3. As for your question : I strongly doubt if your VS.NET can be called "o...

Area chart display ENDS before data ?
Creating Simple area (test) charts - with data ending far before 'date', (hortz.axis); is no problem. Have the 'ending' section - VACANT.(to indicate future values to come). Does not having CONTIGUOUS data prevent that? -- Boswell ...

Data Migration Framework Tools Installation Problems
To whom it may concern: Recently our organization was preparing for an ACT! to CRM migration and had a slight problem getting the Data Migration Framework Tools installed for Microsoft CRM v1.2. We kept getting an error that said something to the effect of "The installation program was interrupted before setup could complete." Upon further deliberation, we discovered a extremely low tech reason for the error........the DMF has to be installed off of the CD and not from a MSI package copied to the hard drive. *slaps forhead* DOH! ...

deleting Exchange-type account in Outlook
Someone told me the last time someone deleted the Exchange account from Outlook 2003 on his machine, he also lost all his e-mail and his entire address book. This does not sound right to me. If the Exchange account is deleted, is there some chance that this other data will also disappear? It depends very much where the email messages are kept. If they are on the Exchange Server, then the messages disappear with the account. If they are downloaded on a local PST file, then they will still be there. runner7@fastmail.fm wrote: > Someone told me the last time someone deleted the Exchange...

journalling Connector data
Hi, ANyone help. We have setup journalling which appears to be working on Mailbox's. We have a External mail system which is configured using a connector. We would like to archive this as it enters from SMTP to the connector. Any ideas how this is done. We need to have these messages journalled also. any help appreciated. Patty Exchange Server (2003/2000) journaling is only available at the mailbox Store level - message journaling doesn't occurr at the transport. Perhaps look into writing a transport event sink or 3rd party solutions. -- Bharat Suneja MVP - Exchange www.zenp...

100% Passing Guaranteed in All I.T. Exams/Certifications at 1st Attempt Hi Friends, To pass all types of I.T Exams/Certifications with mind blowing results in 1st attempt just visit http://www.itreal
100% Passing Guaranteed in All I.T. Exams/Certifications at 1st Attempt Hi Friends, To pass all types of I.T Exams/Certifications with mind blowing results in 1st attempt just visit http://www.itrealexams.com/ and get real time Q&As, Brain Dumps, Real Exams, Study material, E-Books, Video Exams and Labs for your all type of I.T Certification Exams. ...

data by month
I’m having trouble figuring out how to get started on a report that takes a set of currency values in a table and totals them in such a way so that I can break-down total income by month. So if I have 14 different potential values (there will always be some at zero-value) and also have 1 department field that can have 1 of 3 values, how would I structure a query (ultimately leading to a pivot-chart which I’ve never used before) that will total all dollar values for a given month for each of 3 departments? The date range will be no more than a year at a time but may only be 1 m...

External data link change
Hi, I've a sheet linked to an external data on the net, and I would like that a cell in this sheet to indicate the last date and time it was updated, the simplest way the better but I can do some programming too. Thanks for your attention, -- Domingos Junqueira No need of help any more, I solved the question. Thanks again ...

Retrieving address data from a related entity
Hi, Can anyone help me with this please. I have created a custom entity (Suppliers) and created a relationship to the existing contact entity to add contacts to it. My contacts form now has two lookups (account and supplier). If I create a new contact from within an account, then the address information is pulled through since the mappings already exist. However, I am unable to create new mappings between the supplier and contact entitites for the address fields because the contact fields are already being used in a mapping. The issue I have that would be nice to solve, is that when a new co...

How to show the next number depending on other data
Any assistance would be greatly appreciated. Column F is used to enter account names (7 in all & could grow), and Column G is used to enter cheque Nos. Cell F25 onwards is where the first account names are entered and , G25 onwards is where the corresponding cheque numbers of the coorespnding account is entered. My question is..... Is there a way to have other cells down column G (say G30, etc.), show what the next cheque number would be if the same account name is entered in F30 that was shown in F25 (bearing in mind that other accounts and cheque numbers will be entered in the intervenin...

how to get account data from a contact in the opportunity `
Hello crm-gods, how can i get the accountid (guid) from an contact in the opportunity, to set it in an extra field ? This script isn't really giving me the guid, i fear: var oCustomer = new Array(); oCustomer = event.srcElement; var oAccount = crmForm.all.new_account; if (typeof(oAccount) != "undefined" && oAccount != null) { if (typeof(oCustomer) != "undefined" && oCustomer != null) { var id = oCustomer.DataValue[0].id; var typename = oCustomer.DataValue[0].typename; if (typename == "contact") { //Create a new HTTP...

text typed into one cell shows up in another
Has anyone ever had this happen to them? I would type text in cell D1226, hit enter, and the text would diplay in cell Q1226. How does one fix this? I'm using Windows XP and Excel 2007. -- CarenvB Do you have a formula in Q1226? Maybe it's just retrieving the value in D1226? If the value D1226 actually disappears, then you may have a macro that's helping you. CarenvB wrote: > > Has anyone ever had this happen to them? I would type text in cell D1226, > hit enter, and the text would diplay in cell Q1226. How does one fix this? > I'm using Windows XP a...

Exporting data from Visio
Is it possible to export data from Visio that was not imported from a data source? yes al "GMVoices" <anonymous@discussions.microsoft.com> wrote in message news:A3ED53DE-CB79-4EBC-AE60-6E068D0A8548@microsoft.com... > Is it possible to export data from Visio that was not imported from a data source? the answer was just a vague as the question "Al Edlund" <edlund@ameritech.net> wrote in message news:Os6d65frDHA.1084@tk2msftngp13.phx.gbl... > yes > al > "GMVoices" <anonymous@discussions.microsoft.com> wrote in message > news:A3E...

Ctrl + End takes me to no data
On some of my spread sheets, when I press Ctrl + End, I am taken clear down on my spread sheet where there isn't any data. I have highlighted all the rows between the end of my data, and where Ctrl + End takes me, and deleted the rows. Also, I highlighted the rows and selected edit, clear, all. Nothing has solved the problem. Can anyone explain this? Sherry, Don't highlight the cells between your data and where control end took you but in stead between your data and excels last row (65536) and delete. Also : save the file after you deleted these rows (and if necessary : th...

Can I truncate the data in the TaxEntry Table?
I work for a non profit and the Tax column in the TaxEntry table is all 0's. Would it hurt to truncate that table? Is there a way to turn on the tax functionality? It is not hurting anything, except that the table is nearly 3 Gigs in size. Much Thanks! ver2.x I think This is a multi-part message in MIME format. ------=_NextPart_000_0461_01C7CA48.7F2BA010 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: quoted-printable Darwin, 3 gig, I doubt it. Maybe 3 gig for the database or group of RMS tables Put this into a new query in SO Admin to find out t...

Conditional data validation drop downs
Good afternoon, I want to set up drop down's that bring up criteria to be selected based on a previous data drop down selection, i.e. let's say column A has drop downs consisting of various areas within a building, i.e. Ceiling Void, Roof, Ceiling, Floor etc. Based on what is selected in A1 the drop down criteria in B1 should relate only to what could apply to the selection in A1. I.e. If Ceiling Void is selected in A1 then the user should only be allowed to select, for example, from Fire Break, Pipe Lagging, Fire Break, Feature Panels etc in B1. If however 'Roof&#...