Formatting Text as Double with 2 Decimals

[iheartvba]

Hi,
I have got a field with numbers in it, which access automatically reads as text (it is a linked CSV text file). One example of a record in this field is -9.99999999999801E-03, when I do

 Format((Cdbl("-9.99999999999801E-03")),"#.##")

I get the answer as -.01, which should actually be -9.99.


Also if I had a number such as 3 if I did the same format command on it, i see '3.' instead of '3.00' can this be fixed? Maybe it doesn't even matter.


Thanks

[FishVal]

@iheartvba
I don't see what is wrong here.

Also if I had a number such as 3 if I did the same format command on it, i see '3.' instead of '3.00' can this be fixed? Maybe it doesn't even matter.
Thanks

Use "0" instead of "#" in format string where you want to force zeroes.
To read comprehensive help about it open any table in design view, go to format property of any field and hit F1.

[iheartvba]

Hi Fishval,
Thanks for the quick response.

 Format((Cdbl("-9.99999999999801E-03")),"#.##")

Should this not return -9.99?

or does the E-03 move it 3 decimal places to the left?

I don't know why it's showing the E-03

Sorry If I'm confusing you. Sometimes the challenge is actually articulating the question, so thanks for your patience with me, as always

[FishVal]

Floating point .

[Stewart Ross]

Sorry iheartVBA, Fish is rightly pointing out that it is you that is wrong in this case! The E notation tells us that the number is presented in a form of scientific notation, where numbers are represented by decimals to a particular power, as explained in detail in the Wikipedia link Fish provided.

In this case -9.99999...E-03 means -9.99999... * 10^(-3) = -0.0099999... which will round to -0.01 when displayed to two decimal places.

There is no problem at all here, at least not with Access or the CSV anyway...

-Stewart

[NeoPa]

In case you were wondering, the second part of the question is also explained in the second part of post #2.

[iheartvba]

I suspected that about E being for exponent. It must be me doing some incorrect calculation. I couldn't see the Wikipedia link, that FishVal provided. Also with regards to my second question, I just tried it out and using 0 instead of # will give a 0 if there is no preexisting number there.

Many Thanks

[NeoPa]

When formatting, # is generally just a place-holder.
#,##0.00 specifies that digits be grouped in blocks of three with a comma (,) separating. The zero (0) before the dot (.) indicates to display that digit even if zero.

1234567.89 ==> 1,234,567.89
.123456789 ==>         0.12

To show more digits (if available) after the decimal point, add hashes (#,##0.00########).

.123456789 ==>         0.123456789

[iheartvba]

Thanks guys. You have been a great help (as always)