How do I get MS Access to Read Scientfic Numbers from a Text File?

[KmhComputer]

Help Please!

I am writting a Module using VBA in my Access Database.
I have a text file that I have to parse through using string functions like
Left() and Mid(). I am able to pull all of my data out properly with the
exception of Scientific numbers. I am also using VAL() to convert my string
numbers into actual numbers.

Here is an example line I need to read and assign each number to a different
variable. I can pick up the first half of the number and the E and the plus
or minus sign, but the numbers after that are lost. My variables are dim
as Double.

What do I do.? How do I tell the program that this is a Scientific Number?

Is there a better way to do this?

An example:

1.23E-07 2.45E+02 6.99E+10 3.21-06


Any help anyone can give would be greatly appreciated.

Thanks so much,
Kathy

 

[Greg]

Kathy,

I guess I am a little confused about what you want the finished product to be.

do you want 1.23E-07 to look like 0.000000123 or do you want to leave it
in the format 1.23E-07? I am assuming you want it to look like 0.000000123?

 

[Tim Ferguson]

What do I do.? How do I tell the program that this is a Scientific
Number?

Without seeing your code, I don't know how this relates to your problem,
but this seems to work properly:

? CDbl("1.23E+07")
12300000


.... advantages of Strong Typing!


All the best


Tim F

 

[KmhComputer]

The folks I'm working for want to see the numbers in the table as 1.23E-07.

What my program is actually putting in my table is 1.23e-

There is a definite translation issue in trying to pull the initial data
from being characters and translating back into a number. The file I am
pulling information out of is like a written document. It's not a nice and
neat data dump that could be easily imported into a table.


I am working with recordsets and variables. Right now I am pulling my info
by using LEFT() or MID() and then taking the VAL() if the data is supposed to
be a number.


Ex. Code:

RadialNum = Val(Mid(Mystring, 18, 8))


Mystring represents the test field I am trying to read and my scientic
number begins in column 18 and I am reading for length of 8. Then I am
trying to turn that text into a number. RadialNum is dimensioned as Double.


Does that help?

 

[KmhComputer]

The folks I'm working for want to see the numbers in the table as 1.23E-07.

What my program is actually putting in my table is 1.23e-

There is a definite translation issue in trying to pull the initial data
from being characters and translating back into a number. The file I am
pulling information out of is like a written document. It's not a nice and
neat data dump that could be easily imported into a table.


I am working with recordsets and variables. Right now I am pulling my info
by using LEFT() or MID() and then taking the VAL() if the data is supposed to
be a number.


Ex. Code:

RadialNum = Val(Mid(Mystring, 18, 8))


Mystring represents the test field I am trying to read and my scientic
number begins in column 18 and I am reading for length of 8. Then I am
trying to turn that text into a number. RadialNum is dimensioned as Double.


Does that help?

 

[Tim Ferguson]

The folks I'm working for want to see the numbers in the table as
1.23E-07.

Two big problems here:

(1) folks shouldn't be looking at tables AT ALL... if they get anxious
without a row-and-column structure then make them a form and open it in
datasheet mode.

(2) you were originally talking about converting text "1.23E+07" to a
number; now you want to take a numeric value and present it as text.
Nothing wrong with that, of course, but it's a different problem with a
different solution.

You can set the Format property of the text box on the form to something
like "0.000E+00" or "Scientific" (without the quote marks).

Hope that helps


Tim F

 

[KmhComputer]

Actually, I am still trying to see the scientific number that is written in
a text file as a numerical value.

When I strip the number from the text file with the following code:

RadialNum = Val(Mid(Mystring, 18, 8))

it gets written into my table as:

1.23e+ and nothing else. The Numbers following the plus or minus sign
are missing.


The textbox format will help me verifying my information and I appreciate
that help.

Kathy

 

[Tim Ferguson]

RadialNum = Val(Mid(Mystring, 18, 8))

don't use Val, use CDbl


Tim F

 

[John Spencer]

Are you sure that MID(MyString,18,8) is returning the correct string? Have
you tried assigning the string to a variable, so you can check the string
itself.

The val function works fine for me in every case that I've tested.