Format Properties for Number That Contain Powers

I have numbers that I am trying to enter into access to use in conversion 
tables and sometimes the numbers maybe like 1.036368 x 10^-4 or 6.24196 x 
10^19 or even 0.404687....  I am trying to determine what would be the best 
property for a field that contains such a varying array of number schemes.  
Any suggestions would be helpful.
0
Utf
1/21/2010 8:16:01 PM
access 16762 articles. 3 followers. Follow

5 Replies
820 Views

Similar Articles

[PageSpeed] 59

Paul wrote:
> I have numbers that I am trying to enter into access to use in conversion 
> tables and sometimes the numbers maybe like 1.036368 x 10^-4 or 6.24196 x 
> 10^19 or even 0.404687....  I am trying to determine what would be the best 
> property for a field that contains such a varying array of number schemes.  
> Any suggestions would be helpful.

My personal suggestion is to decide on a convention and stick to it, and 
perhaps even write a VBA routine to act like the built-in Format() but 
coercing users' input into a consistent format for saving. It really 
doesn't matter what the convention is but as long it's consistent, 
that's the only thing that counts.

To provide an example suppose we decided to require that all numbers be 
notated in this manner:

X.YYY x 10 ^ Z

and the user input in a say, 0.123 in the textbox. The VBA routine 
should then convert it into this:

1.230 x 10 ^ -1


Or maybe you may prefer to not use the scientific notation and in such 
case, when the user input 1.23 x 10 ^ -3, it should be converted into 
0.00123. The point being it should be consistent throughtout.

HTH.
0
Banana
1/21/2010 8:31:16 PM
Hi Paul,

You will need to use a Number data type, with field size of Single. If you 
click into the Field Size property, and hit F1, you should see 
context-sensitive 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. 
 
You can set a Scientific format, but that's going to apply for all records.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Paul" wrote:

> I have numbers that I am trying to enter into access to use in conversion 
> tables and sometimes the numbers maybe like 1.036368 x 10^-4 or 6.24196 x 
> 10^19 or even 0.404687....  I am trying to determine what would be the best 
> property for a field that contains such a varying array of number schemes.  
> Any suggestions would be helpful.
0
Utf
1/21/2010 8:38:04 PM
Many thanks Tom for the quick reply.  I will try this.

Paul

"Tom Wickerath" wrote:

> Hi Paul,
> 
> You will need to use a Number data type, with field size of Single. If you 
> click into the Field Size property, and hit F1, you should see 
> context-sensitive 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. 
>  
> You can set a Scientific format, but that's going to apply for all records.
> 
> 
> Tom Wickerath
> Microsoft Access MVP
> http://www.accessmvp.com/TWickerath/
> __________________________________________
> 
> "Paul" wrote:
> 
> > I have numbers that I am trying to enter into access to use in conversion 
> > tables and sometimes the numbers maybe like 1.036368 x 10^-4 or 6.24196 x 
> > 10^19 or even 0.404687....  I am trying to determine what would be the best 
> > property for a field that contains such a varying array of number schemes.  
> > Any suggestions would be helpful.
0
Utf
1/21/2010 8:46:01 PM
Tom Wickerath wrote:
> Hi Paul,
> 
> You will need to use a Number data type, with field size of Single. If you 
> click into the Field Size property, and hit F1, you should see 
> context-sensitive 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. 
>  
> You can set a Scientific format, but that's going to apply for all records.
> 
> 
> Tom Wickerath
> Microsoft Access MVP
> http://www.accessmvp.com/TWickerath/

Just so Paul knows - Single or Double is certainly a great & efficient 
way to store a large range of numbers, but if accuracy is essential, one 
should be careful with such data types due to rounding errors inherent 
in those types. However, I seem to recall for Single, it's good up to 7 
digits right, and 15 digits for double (??), and if Paul takes the 
proper precautions in comparing & managing those types, it can be a good 
way without having to get into more complicated representation such as 
Currency, Decimal, combined fields of numeric data to represent integer 
& fractional parts.
0
Banana
1/21/2010 8:46:51 PM
Hi Banana,

I didn't copy that part of the Help file details, but yes, it does show a 
Decimal Precision of 7 for a Single (and 15 for a Double).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Banana" wrote:

> Just so Paul knows - Single or Double is certainly a great & efficient 
> way to store a large range of numbers, but if accuracy is essential, one 
> should be careful with such data types due to rounding errors inherent 
> in those types. However, I seem to recall for Single, it's good up to 7 
> digits right, and 15 digits for double (??), and if Paul takes the 
> proper precautions in comparing & managing those types, it can be a good 
> way without having to get into more complicated representation such as 
> Currency, Decimal, combined fields of numeric data to represent integer 
> & fractional parts.
0
Utf
1/22/2010 4:14:01 AM
Reply:

Similar Artilces:

Help needed changing date formats from m/d/y to d/m/y
I have a spreadsheet with a column of dates in the format ddd m/d/yyyy, e.g. "Mon 9/29/2003". The data has come in as text as I have cut and pasted it from another source. I sort of have something working, but it only works for dates with 2 digits (i.e. from October (10th month) on and from the 10th of each month on), e.g. "Wed 29/10/2003". The formula I have is: =DATE(RIGHT(E3,4),MID(E3,6,2),MID(E3,9,2)) Can anyone help me get this to work for all dates? Thanks in advance. Craig An alternative: Select your column. Choose Data/Text To Columns. Select the Delimited ...

number names
Is there a formula in excel which allows you to convert numbers to th respective number names? e.g. 21 will be retunred as "twenty one -- Message posted from http://www.ExcelForum.com Hi, Not as far as I know. You will probably have to write a macro do it. I'll try to write one and post it to my website at: http://www.geocities.com/excelmarksway Keep an eye out for it in the next couple of days. I can send it to you if you write to me and tell me your number range. excelmarksway@yahoo.com.au regards Mark >-----Original Message----- >Is there a formula in excel which ...

Count the number of hours/durations
Cell A1 ---- Jan 1, 2010 Cell A2 ---- 10:58 [AM] Cell B1 ---- Jan 3, 2010 Cell B2 ---- 22:10 [PM] What is the formula that I can use to give me the results of no of hours between Jan 1, 2010 at 10:58AM to Jan 3, 2010 22:10 PM? Hi Jafferi The following formula does the job: =(A3-A1+A4-A2)*24 This works out precisely how many hours down to the decimal places. If you want the number of whole hours: =int((A3-A1+A4-A2)*24) If you want the number of whole hours rounded to the nearest hour: =round((A3-A1+A4-A2)*24,0) Hope this helps. David "Jafferi" w...

Format Column
I have a column with inmate id numbers in it. I imported them from a text based program. I made a custom formatting for the column because all the id numbers begin with zero. IE 00112356 or 01555666 etc... The format I used was 00000000 under custom. The problem now is that I want to import them into access but access doesn't see the zero at the beginning. How can I make it show the literal number including the zeros? I tried adding the '01222555 before the number, but I would have to manually append 2000 records. Is there a faster way? Morph. Hi you could use a helper column...

Trying to find last 6 digits of variable number
Okay, I have variable number lengths of which I am trying to plumb the last 6 digits of. I.E.: 2389462965 would be 462965 03702362 would be 702362 You guys are great and always have helped me! I thank you all in advance! Government's view of the economy could be summed up in a few short phrases: If it moves, tax it. If it keeps moving, regulate it. And if it stops moving, subsidise it. Ronald Reagan Just use the RIGHT( ) function in conjunction with LEN( ), i.e. assuming the number is in A1: =VALUE(RIGHT(A1,LEN(A1)-6)) The VALUE( ) function ensures you have a numeric value - w...

How do I stop a Textbox in a Userform converting numbers?
Probably a really stupid question... but how do I stop a Textbox on userform from removing the leading zero on a number? eg 01234567890 becomes 123456789 -- Message posted from http://www.ExcelForum.com Alan You must format the cell first as text. e.g Private Sub CommandButton1_Click() Range("A6").NumberFormat = "@" Range("A6").Value = TextBox1.Text End Sub Regards Peter >-----Original Message----- >Probably a really stupid question... but how do I stop a Textbox on a >userform from removing the leading zero on a number? > >eg 01234...

Hide formatting marks
Would anyone be able to advise how I get the formatting marks to be hidden in my return e-mails that I reply to? Please advise Thanks! when you reply to your mail, goto tools-->options-->view and under formatting marks, deselect whichever you dont want to see. regards, Subbu. --- Subramanian .S v-subs@online.microsoft.com Microsoft GPS This posting is provided "AS IS" with no warranties, and confers no rights. ...

Hey, what's the URL to that IEEE floating-point number page?
You know, the one people post when someone doesn't understand why 22 / 10 yields 2.2000000000000000000001 instead of 2.2. Jeff Johnson wrote: > You know, the one people post when someone doesn't understand why 22 / 10 > yields 2.2000000000000000000001 instead of 2.2. > > Goldberg, I presume you mean??? <http://docs.sun.com/source/806-3568/ncg_goldberg.html> -- Hello, > You know, the one people post when someone doesn't understand why 22 / 10 > yields 2.2000000000000000000001 instead of 2.2. You could try : http://en.wikiped...

Excel automatically changes the formatting of the cell to "Time"
Hello, I have a long column of numbers [dates in the YY:DD format]. I wanted to replace ":61" to ":59". Even though the cells are initially formatted as Text, as soon as I make the change, Excel changes the formatting to Time, and the cell with the change now has text ":59:00" in it. Is it possible to force Excel to keep the cells formatted as Text? Thank you! Sam, If the cells are truly formatted as text (Format - Cells - Number - Text), the formatting should never change, and you should always see exactly what you've typed. Give us an example...

Shorten a 7 digit number in a text field
I have a text field with 7 digit numbers in it (none starting with zero) called 'USE_DCAT'. I want to run a query that shortens the 7 digits to the first 3. I am using: Left ([qryRPI.USE_DCAT], 3) but this returns nothing. Any help would be greatly appreciated. Per DLT: >I have a text field with 7 digit numbers in it (none starting with zero) >called 'USE_DCAT'. I want to run a query that shortens the 7 digits to the >first 3. I am using: Left ([qryRPI.USE_DCAT], 3) but this returns nothing. >Any help would be greatly appreciated. The syntax looks unfamiliar...

Cell will not format
Right click on cells that were copied over from another excel sheet and can not format the cell - right click - format does not work Check whether the Worksheet is protected. If this post helps, Click Yes! -------------------- (Ms-Exl-Learner) -------------------- "jeanngoodyear" wrote: > Right click on cells that were copied over from another excel sheet and can > not format the cell - right click - format does not work Right-click>format cells does not show up or you cannot re-format the data? What type of data are in the cells? Maybe dates ...

why no video format for my windows media creating dvd
my creating dvd has only data format; have seen some references to video format as well. this prevents me from copying movies, tv shows to a dvd that can then be shown on another dvd player "webbnc" <webbnc@discussions.microsoft.com> wrote in message news:6A5FE37C-8D1C-42D1-A095-4C34BD721155@microsoft.com... > my creating dvd has only data format; have seen some references to video > format as well. this prevents me from copying movies, tv shows to a dvd > that > can then be shown on another dvd player Your question may be better answered in a news...

Finding Numbers with Cells that also contain letters
Hi All, I have a column of data, the cells are filled with numbers and comments of text, they do not consistantly follow suit i.e the numbers are at the beginning of the cell necessarily. I want to be able to find where the numbers are and extract them into another field. Does anyone know of a formula that can do this? -- Adam ----------- Windows 98 + Office Pro 97 Hi, Adam- I know it can be done with VBA, but I'm not sure about a formula. If you get no answers feel free to contact me at Cyclezen(at)yahoo(d0t)com. The VBA code is simple and direct. Dave O Hi see: http://www.d...

Formatting of email returned.
Hi there, I sent the email below in HTML format using outlook 2007. This email bounced and was sent back to me. I received it in the format below. Why does it appear in this format, where the formatting is all messed up. What are all these characters below. =20. Also look at the subject line below: Subject: =?windows-1250?Q?Raj_Tyagi_- _Work_Order_=28Please_send_confirmation=85=29?= =?windows-1250?Q?_Thank_you.?= Why does it appear like this? What is the solution? Thanks... Ken ---------------------------------------------------------------------------------------------------------------...

NAICS Code Cell Format
Hello, I recently downloaded some US Census data (NAICS codes) into Excel and they have a cell format that I am unable to change. When I sort a list of numbers (e.g. 10, 12, 101, 111, 112), rather than sorting these numbers from lowest to highest (or vice versa), the numbers are sorted as follows: CURRENT SORT 10 101 111 112 12 DESIRED SORT 10 12 101 111 112 The list is being sorted as if the numbers have a hidden decimal after the first two numbers. I have tried to altering the number format to no avail. Does anyone have a suggestion for how I can change the cell format so that the number...

csv format is wrong in Denmark
When I make csv file from excel 2007, then I do not get a comma-separated file. The cells are separated with a semicolon and the the normal dot in numbers is a comma. This is the case when you live in Denmark. The problem with the "Danish" excel csv format is that we can not export it to what ever we want. If I want to use it in Microsoft Visual Studio Team System, for a unit test, then it does understand the format. Is it possible in Office to save in correct cvs format? This is something to be done with the Windows setting. Start>Settings>Control Panel>...

The field 'MyTable.MyField' cannot contain a Null value because the Required property for this field is set to true.
Hello, I have a form in my database used for entering new data into a table. Some of the fields in my table are required, others are not. If I start entering a new record, using the form, but only fill in some of the fields, and then click a button, to, for example, open another form (i.e. I have decided that I do not want to add this new record now), I will get the following error message: The field 'MyTable.MyField' cannot contain a Null value because the Required property for this field is set to true. Enter a value in this field. What I want is for the record not to be added unl...

Why letter l and number 1 are the same in my word document. How c.
To whom it may concern: It's not happen here. But in my word document letter l and number 1 are the same. I meant when I type small case letter l it will apprears as number 1. How can I fix it? Your helps will greatly be appreciated. Thank you very much. Sincerely, Loc The Le (1) This newsgroup is for questions about using Publisher, not Word. They have their own newsgroup. (2) Change your font. -- JoAnn Paules MVP Microsoft [Publisher] "WORD Help In Need !!!" <WORD Help In Need !!!@discussions.microsoft.com> wrote in message news:1103E05B-805A-427C-AB3F-65DB...

Custom number format button
I'd like to create a toolbar button linked to a custom number format so that clicking on the button would re-format the active cell or range to my custom format. Hi Ken, Alt-F11 to enter the VBE Insert | Module Paste the following code: '===========>> Public Sub aTester() Selection.NumberFormat = "#,##0_);[Red](#,##0)" End Sub '<<=========== Alt=F11 to return to Excel View | Toolbars | Customize | Select 'Commands' tab Select 'Macros' in the left-hand Categories window Drag the smiley icon ftom the commands window to your toolbar Ri...

Using formatted cells in formulas
I have been provided a worksheet with a list of 5-digit part numbers that the creator of the worksheet formatted as zip code to preserve leading zeros. I need to use these cells in a formula which uses the "&" operator to tack on a suffix. For example: Original part number cell A1 = 2345 (with zip code formatting displays as 02345) I need cell A2 to be 02345XX, so I'm using the following formula: A1&"XX". Problem is, when I do this, Excel drops the leading zero and displays 2345XX. Is there a way to have Excel use the formatted display string for cell A1 inst...

Why do I get this ###### instead of 11:00 p.m. in formatted cell?
One reason could be that the column isn't wide enough. Try widening the column. -- Biff Microsoft Excel MVP Always supply your formula and the values of the input cells -- Kind regards, Niek Otten Microsoft MVP - Excel "new user" <new user@discussions.microsoft.com> wrote in message news:32404C0C-FB66-4D90-A301-42D0322E2CFE@microsoft.com... | On 15 =F1=E5=EF, 19:43, new user <new u...@discussions.microsoft.com> wrote= : > Not to forget: if cell contains fomula that evaluates time value and result turns to be negative, it will be shown as ###### no...

Frx for Dynamics Btrieve/Pervasive will need a registration number
Hi, I am currently covering for the normal manager of information systems. They currently have great plains 7.00g12 and Frx6.5. Everything was working for years, until today. Today, when trying to use Frx, all users are getting the following error: Frx for Dynamics Btrieve/Pervasive will need a serial number in 27 days. The thing is after this screen they can't use the program at all anymore, yet on my machine I do get the error, but at least can use it. I do have an administrative account though, while they don't. So, I went looking through all of the managers files found...

Windows CE power off message
Hi, Is there a way to detect that a windows CE device is being turned off? The device is running Windows CE .NET version 4.2. We have a data input card attached that doesn't alway recover if we're in the middle of taking a measurement when the power goes off. I'd like to pause measurements before the unit shuts down. Regards, Ask in NG microsoft.public.windowsce.embedded.vc Regards, Guido "mfr" <mfr@community.nospam> schrieb im Newsbeitrag news:41A6E3B8-2A04-4EEA-94EF-09958281B559@microsoft.com... > Hi, > > Is there a way to detect that a window...

cell formatting #7
I have sheet that is not letting me format a number unless I doubl click on the cell. Say the cell value is 5.00000 I cannot reduce the number of decimals. There is over 12k rows of data so I don't particularly want to g through each cell. Thanks in advanc ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Try - tools / options / edit / fixed decimal. >-----Original Message----- > >I have sheet that is not letting me format a number unless I double >clic...

Sending HTML format from Exchange 2007
I am looking for a way to force all messages sent from exchange to go out in an HTML format. We are adding some branding info to outgoing email and it needs to be in HTML format. There are several clients using exchange, so the only logical place to manage things is on the server. Looks like any way to do this may be well hidden. Any suggestions? Thanks, Manuel, I believe that can only be done on the email client. "Manuel" <Manuel@discussions.microsoft.com> wrote in message news:41986B58-3FF5-4738-9DF8-84CF94537E0E@microsoft.com... >I am looking...