Prev: SQL Statement Insert Into
Next: Query based on 2 parameter in 2 diff tables
From: Jeff on 13 Jan 2010 15:55
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.



"Marshall Barton" wrote:

> Jeff wrote:
>
> >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.
>
> --
> Marsh
> MVP [MS Access]
> .
>
From: John W. Vinson on 13 Jan 2010 18:59
On Wed, 13 Jan 2010 12:55:11 -0800, Jeff <Jeff(a)discussions.microsoft.com>
wrote:

>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?
--

John W. Vinson [MVP]
From: Marshall Barton on 13 Jan 2010 19:15
Jeff wrote:
>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)

--
Marsh
MVP [MS Access]
From: Jeff on 14 Jan 2010 08:26
That was it!

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



"Marshall Barton" wrote:

> Jeff wrote:
> >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)
>
> --
> Marsh
> MVP [MS Access]
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: SQL Statement Insert Into
Next: Query based on 2 parameter in 2 diff tables