MS ACCESS QUERY TO CONVERT SCIENTIFIC TO TEXT

[Number11]

How do you convert imported data that is showing as Scientific to text?

 

[arnelgp]

?CDec(theNumber)

 

[MajP]

I interpret it the other way. Your question is not clear.
If the value is actually a number and you want it to be a text in scientific notation in a query then

Select Format([YourNumberField],"Scientific") as NumberInScientificNotation from some table

So you want it to be text in what format?

 

[Number11]

So the data in the excel spreadsheet shows the column is formatted as " General" so the number appears like my 1st point and when this is then imported the number is changed from say 884750 to 884800 seems to be rounding it up, so i need to change this on importing to a text?

 

[arnelgp]

use Double, Decimal or larger fieldtype.

 

[Number11]

use Double, Decimal or larger fieldtype.

so i then get this...2.0000558199e+12

 

[arnelgp]

the question is does it alter the value, or
if you use a Query the value is not same as when
you import it?

 

[Number11]

the question is does it alter the value, or
if you use a Query the value is not same as when
you import it?

thats how it looks in the table its been imported into

 

[arnelgp]

create a query and use Format([field], "#,##0") and check if there is no loss
on your import.

 

[MajP]

so i then get this...2.0000558199e+12

The only reason it is displaying in scientific notation in Access is due to formatting. Format the field in the query properties. It is probably inheriting the formatting from the import.

 

[The_Doc_Man]

so i then get this...2.0000558199e+12

I can't remember the exact cutoff point, but by default (and "general number" format qualifies as a default case) a number will shown as conventional floating-point nnnn.nnn until the potential exponent gets bigger than the "cutoff." After that it switches to e-notation. By the way, the same is true for numbers much smaller than 1. And again, I don't recall the low-side cutoff point - but it is there.

Once you get one of those, you have to explicitly format the number because otherwise Access is going to switch to e-notation for you.