Problems with Decimal Places in Currency Fields

[David Nunn]

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.someFie ld
from table_A,table_B
where table_A.currenc yField=table_B. currencyField

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

I have tried using FORMAT(FieldNam e,"##.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

[Nunya Biznas]

Re: Problems with Decimal Places in Currency Fields

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);

[David Nunn]

Re: Problems with Decimal Places in Currency Fields

On 11 May 2005 11:09:21 -0700, "Nunya Biznas"
<NunyaBiznas@co mcast.net> wrote:
[color=blue]
>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);[/color]

Thanks mate, you're a champ.