Formatting problem in Query Design Grid

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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...
 
hlmcclean said:
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
Click to expand...

Glad it was that easy.
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...
Click to expand...
 
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]
 
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...
 
Back
Top