Change value to one of three possibilities 1

[oticonaus]

I have a report which lists 1 sale person. Each person has multiple lines making up their sale, each with a quantity. Summing the quantity in the report's record source gives a value between -3 to +3. On the report, I need to show -1 if the value is <0, 0 if it is 0, or +1 if the value is >0.

I am sure that there must be an easy way, but I just can't see it. Using iif() doesn't allow for 3 options. I could certainly do it easily enough using vba code, but I am not sure which report event, if any, will allow me to do this. The field is in the Detail section.

 

[dhookom]

Set the format property of the text box to:
"+1";"0";"-1";""

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[oticonaus]

Ah hah! I had to change it slightly to "+1";"-1";"0";"" and then it works a treat.

As is the way of these things, I now have a new problem.

The quantity field is then used in a formula, and is also subtotalled. It seems that the format property only makes the number look different - it doesn't actually change the value of the field on the report?

For example, -2 now displays as -1, but it calculates -1 x 23.5 = -47.

 

[dhookom]

Hey, we met the initial specs of "I need to show" with a little correction of my mistake. You can use the Switch() function
=Switch([yourfield]<0,-1,[YourField]=0,0,[YourField]>0,1)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[oticonaus]

Thanks Duane. I have got it to work now. I needed to move the Switch function from the report to the record source, and that did it.