Currency Field Rounded (1 Viewer)

[tmyers]

I have an interesting issue that I can figure out why it is happening.
In my table tblFixture I have the field UnitCost which is set to the data type Currency, yet when I import data in, this is rounded to the nearest whole number and I thought this datatype would only round to the nearest hundredth. The Excel file that is being imported, the field in question is also formatted to Currency, so I have no idea why this is happening.

Field settings:

Attached is an example file that is being imported and here is a snip of the field in the table after that same file is imported:

What is causing this rounding? I have never had this problem before and from the research I have done, it shouldn't be occurring with this data type.

 

[Gasman]

I just linked to your workbook and created a Make Table query?
Here is the result

I then amended the field to have the currency format. It did not have it before, just defined as currency, so what is yours defined as?

Then I changed the query to an Append query
Result

 

[tmyers]

The problem was I had forgotten that the data lands in a temp table prior to being cleaned up and appended to the final table and that tables field was set to Long which is why the numbers were being rounded.

 

[Gasman]

The problem was I had forgotten that the data lands in a temp table prior to being cleaned up and appended to the final table and that tables field was set to Long which is why the numbers were being rounded.

Well at least you got to the bottom of it.

 

[tmyers]

Yeah, this is the 3rd time in a row the answer to the problem was staring me in the face

 

[Pat Hartman]

The problem was I had forgotten that the data lands in a temp table prior to being cleaned up and appended to the final table and that tables field was set to Long which is why the numbers were being rounded.

Using a "temp" table whether you use the make table or delete/append method ALWAYS leads to database bloat and this requires frequend C&R to cleanup the unrecovered space.

If you can link to the text file/spreadsheet rather than importing it and clean it up using the append to the permanent table query, that is a more efficient solution.

If you can't clean up the data with a query, there is a special technique we use that relies on a template database. You create a database with only the table or tables that will be imported. Define all the columns as short text to avoid losing any data as part of the initial append. C&R and place on the server. Copy the file ONCE to your local hard drive and open your FE ONCE to link to the local table. Then, to start the import process, copy the template db with the empty tables to your local hard drive. It will overlay the previous version if there is one. Since the name is the same for the db as well as the tables and columns, nothing has changed as far as Access is concerned so all you queries and code will just work. The next step is to append the linked source data to the local temp db. Then continue your cleansing process and end by appending to the permanent tables.

 

[ebs17]

However, additional back and forth calculation requires additional time. Because there was talk of larger quantities: Using an index as a motor for performance is usually done with it.
Currency can safely manage 4 decimal places.

 

[tmyers]

Using a "temp" table whether you use the make table or delete/append method ALWAYS leads to database bloat and this requires frequend C&R to cleanup the unrecovered space.

If you can link to the text file/spreadsheet rather than importing it and clean it up using the append to the permanent table query, that is a more efficient solution.

If you can't clean up the data with a query, there is a special technique we use that relies on a template database. You create a database with only the table or tables that will be imported. Define all the columns as short text to avoid losing any data as part of the initial append. C&R and place on the server. Copy the file ONCE to your local hard drive and open your FE ONCE to link to the local table. Then, to start the import process, copy the template db with the empty tables to your local hard drive. It will overlay the previous version if there is one. Since the name is the same for the db as well as the tables and columns, nothing has changed as far as Access is concerned so all you queries and code will just work. The next step is to append the linked source data to the local temp db. Then continue your cleansing process and end by appending to the permanent tables.

I had honestly thought that was what I did, hence me completely forgetting that temp table existed. I have already gone back and begun revising it to remove it as I am in the file doing formatting already, so might as well write the data to the db while I am there.

 

[Cotswold]

I have removed my last response as this wasn't the one I wanted to post to.
But in response to ebs17, as noted only the other week on a forum for a different language. Since Windows XP speed has never been an issue with business software. But I consider 100% accuracy a preference to speed. If the charges on an invoice are a penny out to the total it is unacceptable.
Integer calcs are the only reliable method, in my opinion.