Allen Browne said: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?
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Cathy C said: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.
Thanks.
Cathy
Cathy C said: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 said: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 said: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.
Allen Browne said: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.
Reply to group, rather than allenbrowne at mvps dot org.
Cathy C said: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 said: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?
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.
Allen Browne said: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.
Reply to group, rather than allenbrowne at mvps dot org.
Cathy C said: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 said: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?
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.
Allen Browne said: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.
Reply to group, rather than allenbrowne at mvps dot org.
Cathy C said: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 said: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?
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.
Cathy C said: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 said: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.
Reply to group, rather than allenbrowne at mvps dot org.
Cathy C said: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
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?
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.
Allen Browne said:Makes sense.
Round the calculation to 2 places.
Also good to convert to Currenncy.
CCur() can't handle nulls, so use Nz() as well
MOTOT:CCur(Nz(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)))) ,0))
Cathy C said: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 said: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.
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
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?
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.
Allen Browne said:Oops; forgot the Round() bit:
MOTOT: CCur(Nz(Round( ... ,2), 0))
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Allen Browne said:Makes sense.
Round the calculation to 2 places.
Also good to convert to Currenncy.
CCur() can't handle nulls, so use Nz() as well
MOTOT:CCur(Nz(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)))) ,0))
Cathy C said: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?
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.
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
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?
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.