casting number as currency in expression

[miconian]

So basically, there are two important fields: [SumOfNet Amount] and [Accrual Amount]. They are both data type 'number,' field size 'double.' I want to subtract one from the other, and I want the data type for the result to be 'currency.'

Here's how I'm trying to do it:

SELECT [joint estimates with exclusions].*, Format([SumOfNet Amount]-[Accrual Amount],"Currency") AS [Revenue Loss]
FROM [joint estimates with exclusions] RIGHT JOIN [problem-campaign-only] ON [joint estimates with exclusions].[Capture ID] = [problem-campaign-only].[Capture ID]
WHERE (((Format([SumOfNet Amount]-[Accrual Amount],"Currency"))>0));

But this results in a data type mismatch. What am I doing wrong?

 

[Joe4]

The FORMAT function actually returns Strings, not Numbers, so you do not want to use that in the middle of mathematical functions.

So I think you need to change this:

WHERE (((Format([SumOfNet Amount]-[Accrual Amount],"Currency"))>0));

to this:

WHERE ([SumOfNet Amount]-[Accrual Amount])>0;

FWIW, I would recommend changing the format of the field in your Query/Form/Report instead of trying to do it right in the calculated field anyway.

 

[miconian]

Thanks. Yes, I am going to just the change to the saved import steps, since I have to deal with this on a regular basis.