Formatting problem in Query Design Grid

H

hlmcclean

I used this expression in a query design grid: % Black eth: IIf([Total Black
ethn]=0,"N/A",[Total Black ethn]/[Total Black Ethnicity]*100.

The problem is that I can't format the datasheet output to 2 decimal places
- the "Decimal Places" option does not appear in the properties for this
field! Thank you for any advice offered.
 
H

hlmcclean

Thanks for your reply: the expression used counts from a crosstab field
"Ethnic Group" (made of of various values) that is a text field. I think this
is the source problem, that a string value was being returned. I changed the
expression to % Black eth: IIf([Total Black ethn]=0,"N/A",Round([Total Black
ethn]/[Total Black Ethnicity],4)*100), and this seems to work - was my
analysis right. Thanks, hlmcclean

Joseph Meehan said:
hlmcclean said:
I used this expression in a query design grid: % Black eth:
IIf([Total Black ethn]=0,"N/A",[Total Black ethn]/[Total Black
Ethnicity]*100.

The problem is that I can't format the datasheet output to 2 decimal
places - the "Decimal Places" option does not appear in the
properties for this field! Thank you for any advice offered.
Click to expand...

What is the data type?

Text
Number - Single
Number - Double
Number - Integer
Number - Long Integer
Number - Decimal
Click to expand...
 
J

John Vinson

IIf([Total Black ethn]=0,"N/A",Round([Total Black
ethn]/[Total Black Ethnicity],4)*100), and this seems to work - was my
analysis right. Thanks, hlmcclean
Click to expand...

Yes. The fact that you were returning a text string - "N/A" - as one
of the values of the IIF forces Access to return all values as
Strings, the lowest common denominator. And since you can't Round a
String you would have the problem! Rounding first still gives you a
string, but it's already been fixed.

John W. Vinson[MVP]
 
H

hlmcclean

Thank you - that was a valuable learning point, hlmcclean,

John Vinson said:
IIf([Total Black ethn]=0,"N/A",Round([Total Black
ethn]/[Total Black Ethnicity],4)*100), and this seems to work - was my
analysis right. Thanks, hlmcclean
Click to expand...

Yes. The fact that you were returning a text string - "N/A" - as one
of the values of the IIF forces Access to return all values as
Strings, the lowest common denominator. And since you can't Round a
String you would have the problem! Rounding first still gives you a
string, but it's already been fixed.

John W. Vinson[MVP]
Click to expand...
 
Top