Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Format function failing in SQL query

Discussion in 'Information Technology' started by millardgroups@gmail.com, Jul 28, 2006.

  1. millardgroups@gmail.com

    millardgroups@gmail.com
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    India Indian ‘Journalist’ Slams Gurbaksh Singh Than Admits to Receiving Information from ‘Agencies’ Breaking News Dec 27, 2013
    Opinion West Punjab : Information Pool and Pictures Punjab, Punjabi, Punjabiyat Nov 18, 2013
    When Turban Turns Messenger Diaspora Finds Interesting Ways to Spread Information About Sikhi Sikh Youth Nov 10, 2013
    Story of Transformation Sikh Rehat Maryada Jul 27, 2012
    Sikhism Benti Chaupai - Keertan Format Videos Feb 11, 2012

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    <millardgroups@gmail.com> wrote in message
    news:1149112345.761618.123290@j55g2000cwa.googlegroups.com...
    > 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
    >
     

Share This Page