linked table from txt file

[Guest]

I am linking a table from a txt file into access.
I set the field data type as "single", as I need the numbers in the txt file
However, the numbers that have 2 decimal places do not show correctly in
access table (they are rounded into either 0 or 1 decimal, and are not
accurate)
The integers and numbers that have 4 decimal places are all correct.

What can I do to resolve this? Shall I use another data type?

 

[Guest]

How about using the "decimal" datatype!

 

[Guest]

Thanks a lot Scubadiver.
But when I specify the linked table, the data type list doesn't have
"decimal".....that means the system doesn't allow this data type for linked
table (from txt file)?

 

[Guest]

The number of decimal places can be done with the "fixed" data type. If not,
I have read replies in other posts where the advice was to try "currency".

 

[Guest]

What do you mean by "fixed" data type?
The "currency" data type is ok. But when I export these figures back to
excel. All the figures will be displayed with a "$" symbol in the front,
which I don't want. (although I can change the display format in excel, it's
too troublesome to do it all the time. Actually, if this problem can be
solved, it'll be perfect to use "currency" data type)

Thanks a lot Scubadiver!

 

[Guest]

If your data type is a number or currency (and maybe others) there is a
format option which allows you to choose "fixed". It is another alternative
to decimal.

As far as currency is concerned if you look in the threads I think there is
a format work around to exclude the sign.

 

[Guest]

are u talking about the display format? I am talking about the data type
setting when the figures are imported.

When I import the data, I don't see anywhere that I can choose the decimal
place or format option.

 

[Guest]

We are on the same wavelength.

Are you linking or importing?

I haven't used a text file but if you are linking a text file then I can't
see why you should be losing information (but maybe Access is using pre-set
formats). If you are importing it would be better to set up a table first
before doing so.

 

[Guest]

I am linking.

I don't know why this happens. For example, in the txt file, the number is
123456.78. If I set the data type as "single" in the linked table, I may see
123456.00 or 123455.00

If I use "currency" data type, the figures will be accurate. But the only
problem is the display format when I export the data into excel.

Alternatively, is there any way that I can set the default display format
for "currency" datatype as a pure figure without the "$" symbol in the front?

Thanks a lot for your patience.

 

[Guest]

If you have a linked table I assume you have designed a table?

If you have set up a field and you set the datatype to "number", go to the
"field size" in the bottom pane and change it to "decimal". In the format box
change it to "fixed" and the "decimal places" to 2.

That should work

 

[Guest]

this property cannot be modified for linked table

 

[Guest]

I wouldn't know then. I suggest you start a new thread that includes "need
help from MVP" (which stands for Most Valuable Proffessional)

 

[Guest]

thanks a lot scubadiver

I wouldn't know then. I suggest you start a new thread that includes "need
help from MVP" (which stands for Most Valuable Proffessional)

 

[Guest]

Hi Cynthia,

I wasn't quite able to reproduce what you observed. When I linked to a text
file that contained these entries:

NumericalMeasurement
123456.78
123456.12
123456.23
123456.34
123456.45
123456.56
123456.67
123456.78
123456.89

I got this result, when I choose the Single data type, and opened the linked
table:

NumericalMeasurement
123456.8
123456.1
123456.2
123456.3
123456.5
123456.6
123456.7
123456.8
123456.9

The numbers were rounded to one decimal place, but the first one was not
displayed as you indicated: 123456.00 or 123455.00.

I repeated the experiment, this time choosing Double (not Decimal, as Scuba
indicated, and you confirmed was not in the listing):

NumericalMeasurement
123456.78
123456.12
123456.23
123456.34
123456.45
123456.56
123456.67
123456.78
123456.89

This looks correct to me. Continuing on, I tried specifying Currency. This
is the result that I got:

NumericalMeasurement
$123,456.78
$123,456.12
$123,456.23
$123,456.34
$123,456.45
$123,456.56
$123,456.67
$123,456.78
$123,456.89

I opened the linked table in design view. Although I was prompted with the
message:

"Table 'MyDataCur' is a linked table with some properties that can't be
modified."

I clicked on Yes. I changed the Format and Decimal Places properties in the
lower window from Currency and Auto to General Number and 2, respectively.
The indicated Data Type remains Currency. After saving the changes and
viewing the results, I see the following:

NumericalMeasurement
123456.78
123456.12
123456.23
123456.34
123456.45
123456.56
123456.67
123456.78
123456.89

This looks correct to me too. I exported the data to Excel, using the Office
Links toolbar button, which reads "Analyze It with Microsoft Office Excel".
The data does not display the $ sign. I'm used Access 2003 (11.5614.6568) for
these tests.

Given the choice between using Double and Currency, I would opt for using
Currency and changing the applied format.

Hope this helps.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________