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


From: John Spencer on 13 Jan 2010 13:00
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

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 %)
>
>
From: Marshall Barton on 13 Jan 2010 13:23
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: Jerry Whittle on 13 Jan 2010 13:55
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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"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 %)
>
>
From: Jeff on 13 Jan 2010 13:56
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.



"John Spencer" wrote:

> 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
>
> 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 %)
> >
> >
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: SQL Statement Insert Into
Next: Query based on 2 parameter in 2 diff tables