Linked table number format problem

I have two tables that are linked by a common field called 'reference'. i am running a query and want the query to post a text of "EXCHANGE OV CHANGE" against each record where a field called "ORIG Value", present in both tables, differs by reference number

Expr2: IIf([Bank Gtes]![ORIG_VALUE]<>[BG Last AP]![ORIG_VALUE],"EXCHANGE OV CHANGE","NOT EX")


The BG Last AP table is linked from Excel and the Bank Gtes table is within the Access database.

The problem I have is that the linked table displays it's ORIG_VALUE data with two decimal places - yet when I click in the cell some of the numbers only have one decimal place ie 4569760.1 - the value in the other table for the same reference number is 4569760.10. So these are the same number. Yet when I run the query records like this one have the "EXCHANGE OV CHANGE" text against them. The query thinks they are different due to the additional 0 at the end of the record. As the table is linked how can I change the format so that these records have an additional 0 to give the value 2 decimal places.

I've tried formatting the cells in the linked table but with no joy

Any help would be greatly appreciated!!

thanks

re: Linked table number format problem

You may need to add a field or two to your query to make sure the values are properly formatted, since you are likely comparing text fields.

GtesValue: FormatNumber([Bank Gtes].orig_value, 2)
BGAPValue: FormatNumber([BG Last AP].orig_value, 2)

Then,
Expr2: IIf(GtesValue<>BGAPValue...

re: Linked table number format problem

I like the logic! - However access gives me an Undefined function "Format number" in the expression

any ideas?

thanks

re: Linked table number format problem

FormatNumber should not have a space. If you are using it that way and for some reason it is not available in the version of Access that you have, you could use Format([Bank Gtes].orig_value, "Currency")

re: Linked table number format problem

worked perfectly thanks - you are a star!