 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / April 2008get rid of decimal point
I am doing it manually. the export query joined to a link table and I am using export specifications. The funny thing is that when I export the query, the decimal point does not show up in the resulting text file. But when I add the query to the final query, it is there. If I try to perform a formula on the final query, I am unable to export (greyed out). Crazy! I think I am going to create the make-table query where I am have control over the data format, and then use that table in the final query. This is a 688 byte record and I have a bunch of time already invested in this. Now I have to begin anew. thanks for your help I created a Make-Table query and formated the field to text (I have to modify this everytime I run the query because it changes it back to a decimal field. I linked my new table to my query and I am still getting the $#%^&*'ing decimal point (sample below) 12/30/189911314 193. 1234567890 12/23/199211313 80.0 1234567890 This is a fixed format record that must be exactly as specified (transaction deck). I am ready to smash something! PLEASE HELP! > I am doing it manually. the export query joined to a link table and I am > using export specifications. The funny thing is that when I export the [quoted text clipped - 6 lines] > have to begin anew. > thanks for your help It is usually better to export a query instead of a table, because the query gives you more contorl over how you can format each column. Looking at the middle column 193. and 80.0 - are these the records where you don't want the decimal point? Can you put all the fields that you want to export into a query and post the sample of the results of the query here. (i.e. don't use a make table query at all - put all the export data into a query.) We should be able to suggest how to format that field in the query to remove the decimal point, as long as you post a sample of how you want it to look after it goes into the text file. Jeanette Cunningham >I created a Make-Table query and formated the field to text (I have to > modify this everytime I run the query because it changes it back to a [quoted text clipped - 23 lines] >> have to begin anew. >> thanks for your help Hey Jeanette, I am exporting from a query. But I have the Make-table query (table) linked to the final export. The difference with the 193. & 80.0 column of data is that I have to use the round function to strip off the decimal info. When I export the data from the make table query, the decimal does not show. When I join that table to the final export query, the decimal is there. It seems that the round function is causing problems for me because the fields to the right are numeric field that have a formula applied to them and they do not carry a decimal. 12/30/189911311 193. 143588 143588 12/30/189911311 80. 143588 143588 I changed the regional setting to carry 0 values after the decimal in hopes that this would fix the problem, but you can see all it did was eliminate the zero after the 80. I am going to try to use a formula rather than the round function and see if that will help. regards, > It is usually better to export a query instead of a table, because the query > gives you more contorl over how you can format each column. [quoted text clipped - 37 lines] > >> have to begin anew. > >> thanks for your help Hi, here is a way to do what you want. Here is my test method. Create a new table called tblSnowii 3 fields TheDate - date/time TheNumber - double Field3 - long integer put in this data TheDate TheNumber Field3 5/4/2008 193.25 456789 3/2/2008 80.49 301245 Create a query on tblSnowii Drag all 3 fields on to the query grid In a new column of the query in the Field row type the following Expr1: CInt([TheNumber]) Save the query and switch to datasheet view. You will see that Expr1 contains the numbers 193 and 80 without any decimal point. You can delete the column for TheNumber from the query. You can replace Expr1 with the name you want for the export. Jeanette Cunningham > Hey Jeanette, > I am exporting from a query. But I have the Make-table query (table) [quoted text clipped - 75 lines] >> >> have to begin anew. >> >> thanks for your help The phunny thing is that the decimal point is not there when I look at the result of the query. But when I export it to a text file, the decimal point suddenly appears. I have tried a bunch of things to eliminate it but have not resorted to manually deleting it. (time consuming). > Hi, > here is a way to do what you want. [quoted text clipped - 101 lines] > >> >> have to begin anew. > >> >> thanks for your help Please describe how you are doing the export. When I do the export using the query I described in my previous answer, I get the number without any decimal points in the text file. So it is possible to do what you want, we just need to look more closely at each step you are using. Would you post a couple of sample lines showing the query results before export. You don't need to include all columns, 2 columns with the problem field would be sufficient. Would you list the steps you use when you use from the export wizard. Jeanette Cunningham > The phunny thing is that the decimal point is not there when I look at the > result of the query. But when I export it to a text file, the decimal [quoted text clipped - 124 lines] >> >> >> have to begin anew. >> >> >> thanks for your help Thanks for taking the time Jeanette, step 1: ODBC to table and run a Make-Table query. heres a sample of what the data looks like (w/field format): Comp EmpNo YTDH TSE (t) 0003 (t) 568 (n) I do not modify the format TSE 0012 640 TSE 1287 47 The YTDH is the offending field. Here is the formula to get that data: YTDH:Cint(prearn.ytdhours + .05) (I tried Round, but thought this would eliminate the decimal problem) When I look at the data in table view, there is no decimals. When I run the query to check the data before the text export, there is no decimal. My export specs have the YTDH field as a 4 byte field. Here is what the text export looks like: TSE 0003 568. TSE 0012 640. TSE 1287 47. Thanks again for taking the time to look at this: regards, > Please describe how you are doing the export. > When I do the export using the query I described in my previous answer, I [quoted text clipped - 137 lines] > >> >> >> have to begin anew. > >> >> >> thanks for your help Using your extra info, here is the problem. The field YTDH would be better done slightly differently. My suggestion is to scrap the CInt and the +0.5 Replace with YTDH:CStr([prearn].[ytdhours]) Create a new export spec using a query with Comp, EmpNo, YTDH ( the new YTDH) This will export all 3 fields as text. Exporting YTDH as text, will stop the export process from adding decimal points. Jeanette Cunningham > Thanks for taking the time Jeanette, > step 1: ODBC to table and run a Make-Table query. [quoted text clipped - 177 lines] >> >> >> >> have to begin anew. >> >> >> >> thanks for your help Hey Jeanette, I changed it as you suggested to CStr and the Make-Table query still sets the format to Number. I manually changed the format on design table and when I run the make table query, it changes it back to a number even with the CStr formula. Its as if there are demons screwing with me. I changed the field to 3 bytes which truncates the decimal when the value is 100 or more. I will have to change it to 4 bytes when individuals accumulate 1000 hours for the year. I have never had such a difficult time with a project in my life. Thanks for your help. > Using your extra info, here is the problem. > [quoted text clipped - 191 lines] > >> >> >> >> have to begin anew. > >> >> >> >> thanks for your help Hey Jeanette, the CStr formula brought the values into the table (500.00, 580.98, etc) so I changed the formula to CStr(Round(prearn.YTDH)). It converts the values to a text string in the table. (no decimals) Then when I run the query, there are no decimals visible when I visually check out the output. When I export it to a text file, the %$#@*&% decimal is back. Demons I swear! > Using your extra info, here is the problem. > [quoted text clipped - 191 lines] > >> >> >> >> have to begin anew. > >> >> >> >> thanks for your help Hi, there are several places in this scenario where something can go wrong. 1. when you use a make table query, you have very little control over how the data types are set up. I was assuming (silly thing) that you would export the query I gave you. Instead you used it as the basis for a make table query. 2. The calculated field with CStr([TheField]) needs to be in the final query that is exported. 3. Your last post showed the numbers as integers, so I assumed (again silly thing) that you were dealing with long integers. This post shows that you are dealing with singles. Is this correct? Is prearn.ytdh a single? 4.It would be easier to skip the make table query altogether. Use a table that you have built specially for this purpose. Set up the data types as you want them. Create an export query based on the export table. Each time you want add data to the export table, you can delete all the current data before you add the new data. Here's how: I will call the table tblExport for this discussion. tblExport has 3 fields, the first two are text fields for comp and empno, the 3rd field YTDH is a number field with field size set to single. 5.Create a query based on tblExport In this query use a calculated field called FinalYTDHC: Str((CInt([YTDH])) 6. Export the query Note: the order of the steps is important. The last thing before export is to convert the number to an integer and then a string - no more changes to YTDH before you export it. Jeanette Cunningham > Hey Jeanette, > the CStr formula brought the values into the table (500.00, 580.98, etc) [quoted text clipped - 228 lines] >> >> >> >> >> have to begin anew. >> >> >> >> >> thanks for your help |
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.