Mixed format field

[ksh]

I have the following expression in a query.

Expr1: IIf([TOTAL]<1,"N/A",[Transactions]/[Total])

A sample of the results:
68.8008658008658
N/A
3.63636363636364

Is there any way to format the numbers in this instance to be in the
standard format with two decimals?

I know that the problem is in trying to put two data types in one field, but
in this instance 0 would not be an appropriate value when the division
results in an error.

Thank you for your help on this.

 

[Golfinray]

When you are in query design view, try right clicking on that field to get
properties, and set format.

 

[ksh]

I tried setting the properties, but the options don't appear in the drop down
box. I went ahead and typed in "standard" and that did shift the column to
right alignment, but the line that would normally allow the decimal
specification is missing completely on this field.

:
When you are in query design view, try right clicking on that field to get
properties, and set format.

:
I have the following expression in a query.

Expr1: IIf([TOTAL]<1,"N/A",[Transactions]/[Total])

A sample of the results:
68.8008658008658
N/A
3.63636363636364

Is there any way to format the numbers in this instance to be in the
standard format with two decimals?

I know that the problem is in trying to put two data types in one field, but
in this instance 0 would not be an appropriate value when the division
results in an error.

Thank you for your help on this.

 

[John W. Vinson]

I have the following expression in a query.

Expr1: IIf([TOTAL]<1,"N/A",[Transactions]/[Total])

A sample of the results:
68.8008658008658
N/A
3.63636363636364

Is there any way to format the numbers in this instance to be in the
standard format with two decimals?

I know that the problem is in trying to put two data types in one field, but
in this instance 0 would not be an appropriate value when the division
results in an error.

Thank you for your help on this.

Try

Expr1: IIf([TOTAL]<1,"N/A",Round([Transactions]/[Total], 2))

 

[John Spencer]

One other option is to return NULL instead of N/A. Whether or not that works
for you is dependent on your needs.

IIf([TOTAL]<1,Null,[Transactions]/[Total])

Another option would be to use the format function in the expression

IIf([TOTAL]<1,"N/A",Format([Transactions]/[Total],"#,##0.00;-#,##0.00"))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have the following expression in a query.

Expr1: IIf([TOTAL]<1,"N/A",[Transactions]/[Total])

A sample of the results:
68.8008658008658
N/A
3.63636363636364

Is there any way to format the numbers in this instance to be in the
standard format with two decimals?

I know that the problem is in trying to put two data types in one field, but
in this instance 0 would not be an appropriate value when the division
results in an error.

Thank you for your help on this.

Try

Expr1: IIf([TOTAL]<1,"N/A",Round([Transactions]/[Total], 2))