Display as a percent

[Maquis]

Hi there, I feel like this should be an easy thing to do, however the answer keeps eluding me.

I have a query and this is one of the fields being calculated and displayed:
Group Info: IIf(IsNull([Form1]),Null,100-[Form1])/100

Form1 is an average which is being calculated in a another crosstab query (which is the recordsource for this query)

The calculation works fine and gives me results like this...
.95534244
.894345
.95
1
<null>
.92345
1

However, I would prefer to display the results as percentages (95%, 89%, 100%, etc) rather than decimal figures. I went into the field properties and set the format property to &quot;Percent&quot; but that didn't make a difference. I think the problem may be that Access thinks this is a text field rather than a number field, but I even added a CDbl() to the formula and even that didn't help.

Any ideas?


Maq
<insert witty signature here>

 

[PaulBricker]

Try this.

Group Info: IIf(IsNull([Form1]),Null,Round(100-[Form1]/100,2)) & &quot;%&quot;


Paul

 

[PaulBricker]

Sorry, been one of those days. Didn't quite finish.
Group Info: IIf(IsNull([Form1]),Null,Int(Round(100-[Form1]/100,2)*100)) & &quot;%&quot;


Paul

 

[Maquis]

Hi Paul,

Thanks for the suggestion, but it didn't work. I get &quot;undefined function Round&quot;. (Um, did I mention I'm using Access 97? Perhaps Round didn't get invented until later in life)

Anyways, the error is beside the point. I need to keep this calculation stored as a decimal number because it will be used later in more calculations. I just want to display it as a percent. I'm assuming that tacking the &quot;%&quot; onto the end would turn the whole thing into text.

Maq
<insert witty signature here>

 

[Maquis]

Ok, never mind. I've found a solution that works. I just simplied the formula a bit and now everything displays fine.

Group Info100-[Form1])/100

I could have sworn I had tried this before and gotten errors on the Null values, but I guess I was hallucinating. <Need more coffee>

Thanks for looking at it, Paul. I appreciate it. I absolutely love this site. You wouldn't believe how many problems I've solved without having to ask a question just with the keyword search feature.

Maq
<insert witty signature here>

 

[PaulBricker]

Round wasn't available till A2000. I did a calculation in one of my queries and set the format to Percent and it displayed it just as you would have wanted. That I can't check up on. I would think that 97 would allow you to do that.

Paul