the Expression is changing the data type

[Abby B via AccessMonster.com]

Hi,
I have a number field and an expression in my query to manipulate this number
field by negating the value. The problem is that the expression I created
make the value as text. I tried the format but it only changes the look but
the numbers are still aligned at the left and cannot sort or use it in
another formula.

Here is the Expression:
RX: IIf([Convergence]![Doc_type]="RX",FormatNumber([Amount]*-1),FormatNumber
(0))

Any thoughts,

thanks a lot.

 

[Guest]

Maybe like this --
RX: IIf([Convergence]![Doc_type]="RX",([Amount]*-1),[Amount])

 

[Brendan Reynolds]

The return type of the FormatNumber function is String, so this is to be
expected. The help topic (at least the Office 2003 on-line help topic that I
looked at) doesn't make this clear, but it is clear if you look up the
function in the Object Browser.

You could convert the result using CDbl or CSng, or whatever the appropriate
data type is for your data, or consider removing the calls to the
FormatNumber function.

 

[Rick Brandt]

Hi,
I have a number field and an expression in my query to manipulate
this number field by negating the value. The problem is that the
expression I created make the value as text. I tried the format but
it only changes the look but the numbers are still aligned at the
left and cannot sort or use it in another formula.

Here is the Expression:
RX:
IIf([Convergence]![Doc_type]="RX",FormatNumber([Amount]*-1),FormatNumber
(0))

Any thoughts,

thanks a lot.

Try adding + 0 to the end of the expression (outside the IIF()).

 

[Dirk Goldgar]

Hi,
I have a number field and an expression in my query to manipulate
this number field by negating the value. The problem is that the
expression I created make the value as text. I tried the format but
it only changes the look but the numbers are still aligned at the
left and cannot sort or use it in another formula.

Here is the Expression:
RX:
IIf([Convergence]![Doc_type]="RX",FormatNumber([Amount]*-1),FormatNumber
(0))

Any thoughts,

thanks a lot.

The FormatNumber function returns a String data type, so that's what you
get as a result of your expression. What are you trying to accomplish
by calling the FormatNumber function? From all you've said, I don't see
why your expression isn't just:

RX: IIf([Convergence].[Doc_type]="RX",-([Amount]), 0)

 

[Abby B via AccessMonster.com]

I did that at the beginning but still had the problem so I put the format
formula to make it number!

 

[Abby B via AccessMonster.com]

The +0 at the end worked!

Thanks a lot