Control Select Query Output Field Type

[Jeff]

I have a simple query to generate sales closure rates

Invoiced Rate: Format(([Invoiced]/[Ordered]),"#00.00%")

where Invoiced & Ordered values are linked from other tables. The query
generates the percentages correctly, but the field type is Text. I need the
Output to be numeric (yet retain the %)

 

[John Spencer]

If you don't want a string, then don't use the format function. If you use
the format function you are going to get a string returned.

You can use the format property of a control in a report or form to display
the value as percentage.

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

 

[Marshall Barton]

I have a simple query to generate sales closure rates

Invoiced Rate: Format(([Invoiced]/[Ordered]),"#00.00%")

where Invoiced & Ordered values are linked from other tables. The query
generates the percentages correctly, but the field type is Text. I need the
Output to be numeric (yet retain the %)

Whether a number is a percent or not is not a property of
the number. It's a way to display the number just like a
unit of measure such as inches or degrees. There should not
be any reason to format the values in a query. formatting
should be done in the form/report text box that is used to
display the value.

With all that in mind, the query field should be just
Invoiced / Ordered

And the text box's Format property set to #00.00%

This way the number will never be converted to text and your
problem vanishes into thin air.

 

[Jerry Whittle]

The Format function returns a string. Unfortunately you need the Format
function to display the % sign.

If you are using this query for the record source of a form or report, you
can format it as a percent there.

 

[Jeff]

I changed the Expression to this

Invoiced Rate: Round(([Invoiced]/[Ordered]),4)

and changed the Field Properties to Percent, which produces the output I
need, but now it will not sort by that field. No other fields are sorted.

If you don't want a string, then don't use the format function. If you use
the format function you are going to get a string returned.

You can use the format property of a control in a report or form to display
the value as percentage.

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

I have a simple query to generate sales closure rates

Invoiced Rate: Format(([Invoiced]/[Ordered]),"#00.00%")

where Invoiced & Ordered values are linked from other tables. The query
generates the percentages correctly, but the field type is Text. I need the
Output to be numeric (yet retain the %)

.

 

[Jeff]

I changed the Expression to this

Invoiced Rate: Round(([Invoiced]/[Ordered]),4)

and changed the Field Properties to Percent, which produces the output I
need, but now it will not sort by that field. No other fields are sorted.

I have a simple query to generate sales closure rates

Invoiced Rate: Format(([Invoiced]/[Ordered]),"#00.00%")

where Invoiced & Ordered values are linked from other tables. The query
generates the percentages correctly, but the field type is Text. I need the
Output to be numeric (yet retain the %)

Whether a number is a percent or not is not a property of
the number. It's a way to display the number just like a
unit of measure such as inches or degrees. There should not
be any reason to format the values in a query. formatting
should be done in the form/report text box that is used to
display the value.

With all that in mind, the query field should be just
Invoiced / Ordered

And the text box's Format property set to #00.00%

This way the number will never be converted to text and your
problem vanishes into thin air.

 

[John W. Vinson]

I changed the Expression to this

Invoiced Rate: Round(([Invoiced]/[Ordered]),4)

and changed the Field Properties to Percent, which produces the output I
need, but now it will not sort by that field. No other fields are sorted.

How are you doing the sorting? This Number value will sort numerically (e.g.
8.65% will sort before 10.50%) and shouldn't cause any problems. What's the
context? Could you post the entire SQL of the query, and indicate what results
you are getting and what you expect?

 

[Marshall Barton]

I changed the Expression to this

Invoiced Rate: Round(([Invoiced]/[Ordered]),4)

and changed the Field Properties to Percent, which produces the output I
need, but now it will not sort by that field. No other fields are sorted.

You can not sort on a field alias name. Either sort by the
field's position number in the select list (2 or whatever)
or sort by the expression used to calculate the field (i.e.
Round(([Invoiced]/[Ordered]),4)

 

[Jeff]

That was it!

I restated the field alias name with their source expressions it is working
as expected - Thanks.

I changed the Expression to this

Invoiced Rate: Round(([Invoiced]/[Ordered]),4)

and changed the Field Properties to Percent, which produces the output I
need, but now it will not sort by that field. No other fields are sorted.

You can not sort on a field alias name. Either sort by the
field's position number in the select list (2 or whatever)
or sort by the expression used to calculate the field (i.e.
Round(([Invoiced]/[Ordered]),4)