Problem with decimal places in an imported spreadsheet

[DC27]

Hi,

I have a number of spreadsheets i receive from suppliers on a regular database that i have linked into my access 2010 database.

The spreadsheets contain prices of stock we supply to customers, but some of these prices have more than 2 decimal prices.

All the spreadsheets from the suppliers get appended into a main data table, tblAlldata. The field that the supplier price is appended into is set to currency, 2 decimal places - this seems to have no effect on how the data is appended in though, as it always contains many thousands of records that have more than 2 decimal places.

Is there anyway to append the data in with 2 decimal places
or
Can i apply a bit of code that will set the price on all data within tblAlldata to 2 decimal places

thanks for any suggestions

David

 

[Kiwiman]

Howzit

I would import the data to a staging table, Then append the staging table to the main table using the round function on the field that may have more than 2 dp.

Alternatively you can run an update query after the import to update the offending field:

UPDATE Yourtable SET [Yourtable]![Yourfield] = Round([Yourtable]![yourfield],2)