Expression returning text instead of numbers

[dianemarie]

Hello, we have a report that allows the user to select what type of quantities he wants via a parameter. I did this using an expression, which I drop on the report. However, even though the fields being used in the expression are integers, the expression is seen as text. Therefore I can't subtotal on this field, or format the field. (These fields are in the thousands and I want to see thousand separators.) The expression is:

=iif(Parameters!QtyType.Value = "GNO",Fields!GrossTotalOrdered.Value,
iif(Parameters!QtyType.Value = "GNS",Fields!GrossTotalShipped.Value,
" "))

Am I going about this all wrong, or is there a way to format the results of the expression as numeric with a thousands separator? Thank you for any help.

 

[xlbo]

It is coming out as text because of the last condition which is text (" "). Try this:

=iif(Parameters!QtyType.Value = "GNO",Fields!GrossTotalOrdered.Value,
iif(Parameters!QtyType.Value = "GNS",Fields!GrossTotalShipped.Value,0))




Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dianemarie]

This worked for totaling the field. Thank you! I still can't format it however, with a thousand separator, still working on that. Thanks Geoff.

 

[xlbo]

try this:

=iif(Parameters!QtyType.Value = "GNO",Fields!GrossTotalOrdered.Value,
iif(Parameters!QtyType.Value = "GNS",Fields!GrossTotalShipped.Value,cdbl(0)))

How are you trying to apply the thousands seperator?

#,##0.0

or similar?


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dianemarie]

I'm going for #,###. No decimals. It took it without error but did not update the field with the thousands comma. The number remains #### (currently there are no decimals so that's good).

 

[xlbo]

try using #,##0

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question