Error in expression using Round function.

[PaulA]

Hi, all--

I am using the following expression in a report text box and getting an error:

=IIf(IsNull([BMI-2]),"N/A",Round([BMI-2],1))

I am wanting to have decimals fixed at one place and, this being an expression, I get varying decimal places. I thought the "round" function would be used, but I'm getting the error.

Suggestions?

Thanks.

 

[Brianwarnock]

You do not say what the error is.

Is BMI-2 the calculation or the name of the control.

Brian

 

[PaulA]

Good point. I get "#Error" in the field and the BMI-2 is the name of the control.

Thanks.

 

[SOS]

Rename the control txtBMI2 and then still use the FIELD name: [BMI-2] in the IIf statement.

On reports you need to use the field and not the control, if bound.

Also, don't use special characters in your names like that. That can cause issues even if you bracket, depending on where it is. The only character to use is an underscore ( _ ) if you need to.

 

[PaulA]

Again, good points.
Thanks.

 

[PaulA]

Ii made the suggested changes and am still getting the error.

Here's the expression:

=IIf(IsNull([BMI2]),"N/A",Round([BMI2],1))

 

[SOS]

Did you actually change the FIELD names to be BMI2 in the table?

 

[PaulA]

I changed the field names in the query. The query field itself has an expression:

BMI2: IIf([MinofAssessDate]=[MaxofAssessDate],"N/A",[BMIL]).

The report control name is txtBMIL.

 

[Brianwarnock]

Di it all in the query, as it stands you could end up trying to round "N/A".

BMI2: IIf([MinofAssessDate]=[MaxofAssessDate],"N/A",IIf(IsNull([BMIL]),"N/A",Round([BMIL],1)))

Brian

 

[Rich]

How can you put a text value in a numeric field?

 

[Brianwarnock]

BMI2 will be a text field.

Brian

 

[Rich]

Then how can it be Rounded?

 

[Brianwarnock]

Ah! the mysteries of ACCESS, obviously its by magic, but it does.

Brian