Linked table format query problem

[grego9]

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

 

[pbaldy]

You could try wrapping each value in CCur() (or the appropriate conversion function) to force it to a numeric format.

 

[grego9]

How do I do that? - within excel or access?

thanks

 

[pbaldy]

From the look of it, I'd do it right there in the query.

 

[grego9]

I've put the following in:

GtesValue: CCur([Bank Gtes].[ORIG_VALUE])

BGAPValue: CCur([BG Last AP].[ORIG_VALUE])

EXCHANGE: IIf([GtesValue]<>[BGAPValue],([Bank Gtes]![GBP_VALUE]-([GtesValue]/([BGAPValue]/[BG Last AP]![GBP_VALUE]))),0)

It pops up with a prompt box for a value for GtesValue as if it can't calculate a value for it

any ideas?

thanks

 

[Scooterbug]

If you are defining something in a query, you cannot use it in an expression. Simply replace the name with the actual defination. For example:

Exchange: iif(CCur([bank gtes].[orig_value]))<>CCur(bg last ap].[orig_value]), etc...

 

[grego9]

Worked a treat - you are a star

 

[pbaldy]

You actually can use a calculated field in an expression, though at some level of complexity it will fall over and you need to do as Scooterbug suggests. What you can never have is the Alias of the calculated field in the WHERE clause, like:

WHERE GtesValue > 0

 

[grego9]

thanks for your help. I would have spent ages on this!