By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,687 Members | 2,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,687 IT Pros & Developers. It's quick & easy.

Problems with Decimal Places in Currency Fields

P: n/a
Need to preface that I am not much of an Access type. Doing

I have several tables that have currency fields, which I run queries
against to compare the figures in both. The data is imported from
Excel spreadsheets supplied by different parties, over which I have no
control.

My problem is that whilst several parties supply the spreadsheets with
the currency fields containing two figures to the right of the decimal
place (ie 123.51, this is good, and correct), one party supplies the
spreadsheet with three or four numbers to the right of the decimal
place (ie 123.512, bad). When I run my select queries, such as :

select table_A.someField
from table_A,table_B
where table_A.currencyField=table_B.currencyField

I miss out on a considerable amount of data because
table_A.currencyField is 123.51 and table_B.currencyField is 123.512,
and thus they do not equal each other.

I have tried using FORMAT(FieldName,"##.00") in the select statement,
to no avail. I have also set the Decimal Place properties of the
relevant columns to two decimal places, and Format to Currency (and
tried Fixed as well), but nothing.

Is there a way I can force Access to round the decimal place to two
digits when the data is imported? Or some query I can run to do this?

Thanks,

David Nunn
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Run an update query after you import the data to round the appropriate
fields.

The SQL view would look something like this:
UPDATE Table1 SET Table1.Field1 = Round([Table1]![Field1],2);

Nov 13 '05 #2

P: n/a
On 11 May 2005 11:09:21 -0700, "Nunya Biznas"
<Nu*********@comcast.net> wrote:
Run an update query after you import the data to round the appropriate
fields.

The SQL view would look something like this:
UPDATE Table1 SET Table1.Field1 = Round([Table1]![Field1],2);


Thanks mate, you're a champ.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.