Format function failing in SQL query

[millardgroups]

Can you help me with using the Format function in a SQL query?

I have a query that calculates a range of summary values. The results
currently show a value like 28.20545. I would like them to show as
something like 28.2%.

I've tried using the Format function to make this happen, but I keep
getting an error.

Here's the current query:

SELECT
A.[Leader Positions],
B.[Positions without backup],
(B.[Positions without backup]/A.[Leader Positions])*100 AS [Percent

without backup]

FROM
[SELECT Count([t_Eng Leadership Master].[Leader Name]) AS [Leader
Positions]
FROM (SELECT
DISTINCT [Leader Name]
FROM [t_Eng Leadership Master])
AS [Leadership Position Data]].
AS A,
[SELECT Count([t_Eng Leadership Master].[Leader Name]) AS
[Positions without backup]
FROM (SELECT
DISTINCT [Leader Name]
FROM [t_Eng Leadership Master]
WHERE [Candidate Name] IS NULL)
AS [Leadership Position Data]].
AS B;

I've tried changing the select, as follows:

Format((B.[Positions without backup]/A.[Leader
Positions]),"Percent") AS [Percent
without backup]

I've also tried,

Format((B.[Positions without backup]/A.[Leader Positions]),"0.0%")
AS [Percent
without backup]

Neither of these work.

Can you help?

Thanks!

Scott

 

[Allen Browne]

Scott, the Format() function generates a string. That means you can no
longer use the field for sorting, for criteria, or for mathematical
calculations such as averaging.

If you right-click the field in query design and choose Properties, you can
set its Format property to: Percent.

Realistically, though, it is better to do the formatting in the form or
report, and not expect the query to be a user interface.