Re: Sum not totalling correctly

Just found out the total field is the sum of a calculated field:
MOTOT:
IIf([PPAYFREQ]="B",[PAMT]*26/12,IIf([PPAYFREQ]="M",[PAMT]*12/12,IIf([PPAYFREQ]="S",[PAMT]*24/12,IIf([PPAYFREQ]="W",[PAMT]*52/12,0))))

At what point do I need to perform the rounding? Or, do I need to designate
it as currency in the formula rather than in the form?

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:eGzgiX70FHA.3188@xxxxxxxxxxxxxxxxxxxxxxx
> The Currency data type stores the value to 4 decimal places, though it
> typically displays only 2 places.
>
> So, you probably want to round each entry in the table to 2 places, and it
> will then add up correctly. To do that:
> 1. Create a query into this table.
>
> 2. Change it to an Update query. (Update on Query menu.)
> Access adds an Update row to the query design grid.
>
> 3. Drag the field into the grid.
>
> 4. In the update row,enter:
> Round([Amount],2)
> replacing "Amount" with the name of your field.
>
> 5. Run the query.
>
> --
> 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.
>
> "Cathy C" <cathy-remove-christian@xxxxxxxxxxxxx> wrote in message
> news:OpjrIH50FHA.2924@xxxxxxxxxxxxxxxxxxxxxxx
>> Your presumptions are correct. Just off a penny on some records, not all.
>> Could be rounding, but I would think setting all relevant fields to
>> currency should resolve problem. Any suggestions?
>> C
>> "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
>> news:uW3YX940FHA.3756@xxxxxxxxxxxxxxxxxxxxxxx
>>> Presumably you have a text box in the Form Footer section of your
>>> subform, and its Control Source is something like this:
>>> =Sum([Amount])
>>> where Amount is the name of the Currency field you are trying to Sum.
>>>
>>> That should work, and give you the total for the rows in the subform,
>>> once the row you are working on has been saved. (To verify it is saved,
>>> try moving the cursor into the next row of the subform.)
>>>
>>> There could be rounding errors. Is that what you are seeing? Or is the
>>> value way off?
>>>
>>> "Cathy C" <cathy-remove-christian@xxxxxxxxxxxxx> wrote in message
>>> news:OES9HW40FHA.2792@xxxxxxxxxxxxxxxxxxxxxxx
>>>>I have a subform that is not adding detail items correctly. They're all
>>>>currency set to Auto. Any suggestions? It's not all records, just some.
>
>


.

Relevant Pages

  • Re: sum is incorrect (slightly off)
    ... It is sales, yes. ... you can use the Currency data type. ... When I take grouping off (no sum), ... Here is the SQL for the query that sums the sales figure: ...
    (microsoft.public.access.queries)
  • Re: Sum one field in multi field query
    ... number to currency, to autonumber. ... I would like to sum just one of the fields in this query. ...
    (microsoft.public.access.queries)
  • change currency symbol
    ... my data property of my amount field is currency. ... when i query the sum of the ... amount a dollar sign will represent the whole sum. ... sign to any currency symbol or if not just omit it. ...
    (microsoft.public.access.queries)
  • RE: Criteria
    ... is the datatype of that field in your table set to currency? ... Is it rounding or not rounding? ... amount) on a query ...
    (microsoft.public.access.queries)
  • RE: Criteria
    ... It is set to currency, with 2 decimal places and it is not rounding at all. ... "Golfinray" wrote: ... amount) on a query ...
    (microsoft.public.access.queries)
Loading