calculated currency field sorts like text instead of numeric

[Paul]

I have a simple calculated field in a query based on the following
expression:

$/sf: IIf([SqFt]<>0,Format([Rent]/[SqFt],"Currency"),"")

In the query, Rent is set to Currency format, and SqFt is set to Standard
format. The calculated field, $/sf, won't let me choose the format in the
properties drop menu, so I'm using the Format( . . . "Currency") to tell the
query this vaule should be treated as a number.

I'm using this query in a subform datasheet, and if I try to sort by the
$/sf column, it sorts the values as if they were text as shown in the list
below:

1.45
1.56
16.21
2.05

which is what you would want if these were text values, but not currency.

If I try to sort this column directly from the query, by putting "Ascending"
in the Sort box, I get an error message saying

ORDER BY clause (IIf([SqFt]<>0,Format([Rent]/[SqFt],"Currency"),"")
confilcts with DISTINCT.

What can I do to overcome this problem?

Thanks in advance,

Paul

 

[Duane Hookom]

Don't return strings from your expression if you want to sort by a numeric
value. Formatting should be applied in your form or report control.

DolPerSqFt: Val(IIf([SqFt]<>0,[Rent]/[SqFt],0))

 

[John W. Vinson]

I have a simple calculated field in a query based on the following
expression:

$/sf: IIf([SqFt]<>0,Format([Rent]/[SqFt],"Currency"),"")

In the query, Rent is set to Currency format, and SqFt is set to Standard
format. The calculated field, $/sf, won't let me choose the format in the
properties drop menu, so I'm using the Format( . . . "Currency") to tell the
query this vaule should be treated as a number.

But that's not what it does.

The Format() function returns a Text value. That's what it's designed to do -
turn a number (or any datatype, actually) into a formatted string.

If you want the value treated as a number, just calculate it as a number, and
set the Format property of the Form or Report control in which you display
that number.

The "" in the IIF will cause the same problem - if either returned argument of
IIF is a string, both will be strings. Try

IIF([SqFt] <> 0, [Rent]/[SqFt], NULL)

 

[John Spencer MVP]

Or use Null instead of a zero-length string ("") if you want a "blank" to
appear as the result.

DolPerSqFt: Val(IIf([SqFt]<>0,[Rent]/[SqFt],Null))

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

 

[Paul]

Well, I tried all three suggestions, and found that

DolPerSqFt: Val(IIf([SqFt]<>0,[Rent]/[SqFt],0)) and
DolPerSqFt: Val(IIf([SqFt]<>0,[Rent]/[SqFt],Null))

produced the error message "too complex to be evaluated."

while IIF([SqFt] <> 0, [Rent]/[SqFt], NULL) produced the numeric result.

Nevertheless, all of your comments and explanations helped me to understand
how I managed to create a string value that wouldn't sort like a number.

My thanks to Duane Hookom, John Spencer and John Vinson.

Paul