 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / June 2007linked table from txt file
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? How about using the "decimal" datatype! > 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 [quoted text clipped - 4 lines] > > What can I do to resolve this? Shall I use another data type? 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)? > How about using the "decimal" datatype! > [quoted text clipped - 6 lines] > > > > What can I do to resolve this? Shall I use another data type? 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". > Thanks a lot Scubadiver. > But when I specify the linked table, the data type list doesn't have [quoted text clipped - 11 lines] > > > > > > What can I do to resolve this? Shall I use another data type? 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! > 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". [quoted text clipped - 14 lines] > > > > > > > > What can I do to resolve this? Shall I use another data type? 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. > What do you mean by "fixed" data type? > The "currency" data type is ok. But when I export these figures back to [quoted text clipped - 23 lines] > > > > > > > > > > What can I do to resolve this? Shall I use another data type? 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. > 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 [quoted text clipped - 30 lines] > > > > > > > > > > > > What can I do to resolve this? Shall I use another data type? 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. > are u talking about the display format? I am talking about the data type > setting when the figures are imported. [quoted text clipped - 36 lines] > > > > > > > > > > > > > > What can I do to resolve this? Shall I use another data type? 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. > We are on the same wavelength. > [quoted text clipped - 45 lines] > > > > > > > > > > > > > > > > What can I do to resolve this? Shall I use another data type? 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 > I am linking. > [quoted text clipped - 59 lines] > > > > > > > > > > > > > > > > > > What can I do to resolve this? Shall I use another data type? this property cannot be modified for linked table > If you have a linked table I assume you have designed a table? > [quoted text clipped - 67 lines] > > > > > > > > > > > > > > > > > > > > What can I do to resolve this? Shall I use another data type? I wouldn't know then. I suggest you start a new thread that includes "need help from MVP" (which stands for Most Valuable Proffessional) > this property cannot be modified for linked table > [quoted text clipped - 69 lines] > > > > > > > > > > > > > > > > > > > > > > What can I do to resolve this? Shall I use another data type? 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) [quoted text clipped - 72 lines] > > > > > > > > > > > > > > > > > > > > > > > > What can I do to resolve this? Shall I use another data type? 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 __________________________________________ > I am linking. > [quoted text clipped - 9 lines] > > Thanks a lot for your patience. ______________________________________________ > > We are on the same wavelength. > > [quoted text clipped - 4 lines] > > formats). If you are importing it would be better to set up a table first > > before doing so. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.