Welcome to SPN

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

Sign Up Now!

Format Currency

Discussion in 'Information Technology' started by Gina, Jul 28, 2006.

  1. Gina

    Gina
    Expand Collapse
    Guest

    The following scripts are in 'Make Table' queries. Each script is identical
    in function except one multiples by 90% and the other multiplies by 100%.
    The 100% when the table is created leaves the field format at currency.
    However, the one with 90% changes it to a number format. Any reasoning
    behind this?

    SELECT ARBAL.Collector, Sum(ARBAL.[Cust AR Balance]) AS Six, Sum([Cust AR
    Balance]*0.9) AS [Six Forecast] INTO [Coll 6]
    FROM ARBAL
    GROUP BY ARBAL.Collector
    HAVING (((ARBAL.Collector)="6") AND ((Sum(ARBAL.[Cust AR Balance]))>10.89))
    WITH OWNERACCESS OPTION;


    SELECT ARBAL.Collector, Sum(ARBAL.[Cust AR Balance]) AS Four, Sum([Cust AR
    Balance]*1) AS [Four Forecast] INTO [Coll 4]
    FROM ARBAL
    GROUP BY ARBAL.Collector
    HAVING (((ARBAL.Collector)="4") AND ((Sum(ARBAL.[Cust AR Balance]))>10.89))
    WITH OWNERACCESS OPTION;

    Thanks.
    --
    Gina
     
  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. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Easiest solution would be to use the CCur function:

    SELECT ARBAL.Collector, Sum(ARBAL.[Cust AR Balance]) AS Six, CCur(Sum([Cust
    AR
    Balance]*0.9)) AS [Six Forecast] INTO [Coll 6]
    FROM ARBAL
    GROUP BY ARBAL.Collector
    HAVING (((ARBAL.Collector)="6") AND ((Sum(ARBAL.[Cust AR Balance]))>10.89))
    WITH OWNERACCESS OPTION;


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Gina" <Gina@discussions.microsoft.com> wrote in message
    news:EF53950C-217A-4ED0-BF11-05DBA42E58FC@microsoft.com...
    > The following scripts are in 'Make Table' queries. Each script is

    identical
    > in function except one multiples by 90% and the other multiplies by 100%.
    > The 100% when the table is created leaves the field format at currency.
    > However, the one with 90% changes it to a number format. Any reasoning
    > behind this?
    >
    > SELECT ARBAL.Collector, Sum(ARBAL.[Cust AR Balance]) AS Six, Sum([Cust AR
    > Balance]*0.9) AS [Six Forecast] INTO [Coll 6]
    > FROM ARBAL
    > GROUP BY ARBAL.Collector
    > HAVING (((ARBAL.Collector)="6") AND ((Sum(ARBAL.[Cust AR

    Balance]))>10.89))
    > WITH OWNERACCESS OPTION;
    >
    >
    > SELECT ARBAL.Collector, Sum(ARBAL.[Cust AR Balance]) AS Four, Sum([Cust AR
    > Balance]*1) AS [Four Forecast] INTO [Coll 4]
    > FROM ARBAL
    > GROUP BY ARBAL.Collector
    > HAVING (((ARBAL.Collector)="4") AND ((Sum(ARBAL.[Cust AR

    Balance]))>10.89))
    > WITH OWNERACCESS OPTION;
    >
    > Thanks.
    > --
    > Gina
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 15 May 2006 12:07:02 -0700, Gina
    <Gina@discussions.microsoft.com> wrote:

    >The following scripts are in 'Make Table' queries. Each script is identical
    >in function except one multiples by 90% and the other multiplies by 100%.
    >The 100% when the table is created leaves the field format at currency.
    >However, the one with 90% changes it to a number format. Any reasoning
    >behind this?


    Any multiplication or division operation converts the values to Double
    (to avoid losing precision). Wrap the expression in CCur() to convert
    the Double result back to Currency.

    One question: Why a MakeTable query? You can base a Form, Report,
    export, new query, ..., directly on a Select query; it's very rare
    that there is any real need to go through the complexity and overhead
    of creating a new table!

    John W. Vinson[MVP]
     
  5. Gina

    Gina
    Expand Collapse
    Guest

    Douglas,

    Thank you, it worked great.
    --
    Gina


    "Douglas J Steele" wrote:

    > Easiest solution would be to use the CCur function:
    >
    > SELECT ARBAL.Collector, Sum(ARBAL.[Cust AR Balance]) AS Six, CCur(Sum([Cust
    > AR
    > Balance]*0.9)) AS [Six Forecast] INTO [Coll 6]
    > FROM ARBAL
    > GROUP BY ARBAL.Collector
    > HAVING (((ARBAL.Collector)="6") AND ((Sum(ARBAL.[Cust AR Balance]))>10.89))
    > WITH OWNERACCESS OPTION;
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Gina" <Gina@discussions.microsoft.com> wrote in message
    > news:EF53950C-217A-4ED0-BF11-05DBA42E58FC@microsoft.com...
    > > The following scripts are in 'Make Table' queries. Each script is

    > identical
    > > in function except one multiples by 90% and the other multiplies by 100%.
    > > The 100% when the table is created leaves the field format at currency.
    > > However, the one with 90% changes it to a number format. Any reasoning
    > > behind this?
    > >
    > > SELECT ARBAL.Collector, Sum(ARBAL.[Cust AR Balance]) AS Six, Sum([Cust AR
    > > Balance]*0.9) AS [Six Forecast] INTO [Coll 6]
    > > FROM ARBAL
    > > GROUP BY ARBAL.Collector
    > > HAVING (((ARBAL.Collector)="6") AND ((Sum(ARBAL.[Cust AR

    > Balance]))>10.89))
    > > WITH OWNERACCESS OPTION;
    > >
    > >
    > > SELECT ARBAL.Collector, Sum(ARBAL.[Cust AR Balance]) AS Four, Sum([Cust AR
    > > Balance]*1) AS [Four Forecast] INTO [Coll 4]
    > > FROM ARBAL
    > > GROUP BY ARBAL.Collector
    > > HAVING (((ARBAL.Collector)="4") AND ((Sum(ARBAL.[Cust AR

    > Balance]))>10.89))
    > > WITH OWNERACCESS OPTION;
    > >
    > > Thanks.
    > > --
    > > Gina

    >
    >
    >
     
  6. Gina

    Gina
    Expand Collapse
    Guest

    When I tried to creat a report based on the query, none of the data would
    show. So I thought it might be because the data was calculated and not stat.
    Not really sure, I was just trying an alternative since it didn't work the
    Form based off the query way. I have created many reports based off queries,
    so I am not really sure why the data wouldn't show here.
    --
    Gina


    "John Vinson" wrote:

    > On Mon, 15 May 2006 12:07:02 -0700, Gina
    > <Gina@discussions.microsoft.com> wrote:
    >
    > >The following scripts are in 'Make Table' queries. Each script is identical
    > >in function except one multiples by 90% and the other multiplies by 100%.
    > >The 100% when the table is created leaves the field format at currency.
    > >However, the one with 90% changes it to a number format. Any reasoning
    > >behind this?

    >
    > Any multiplication or division operation converts the values to Double
    > (to avoid losing precision). Wrap the expression in CCur() to convert
    > the Double result back to Currency.
    >
    > One question: Why a MakeTable query? You can base a Form, Report,
    > export, new query, ..., directly on a Select query; it's very rare
    > that there is any real need to go through the complexity and overhead
    > of creating a new table!
    >
    > John W. Vinson[MVP]
    >
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 15 May 2006 14:40:02 -0700, Gina
    <Gina@discussions.microsoft.com> wrote:

    >When I tried to creat a report based on the query, none of the data would
    >show. So I thought it might be because the data was calculated and not stat.
    > Not really sure, I was just trying an alternative since it didn't work the
    >Form based off the query way. I have created many reports based off queries,
    >so I am not really sure why the data wouldn't show here.


    Hrm. Maybe it's a parameter query (with a form reference as a
    criterion maybe?) and the parameter wasn't available, or wasn't
    correct?

    John W. Vinson[MVP]
     

Share This Page