By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,052 Members | 1,551 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,052 IT Pros & Developers. It's quick & easy.

Sum of calculated values in datasheet form

P: 68
In a datasheet form I have the following Text Boxes. This datasheet form is embedded in a main form where the formula ID is linked. The datasheet forms record source is bound to a Query named Q_Formula.

1. txt_FormulaID stores formula ID.
2. txt_ID stores component ID.
3. txt_ComponentName stores component name.
4. txt_QTY allows user to enter quantity. Data Control Source bound to field QTY.
5. txt_UOM allows user to choose unit of measure, i.e., Lb, Oz, Kg, or G. Data Control Source bound to field Unit.
6. txt_Conv has Data Control Source bound to a calculated field, ConvWT, in the Query. The calculation is to convert the value in the txt_QTY to a new value based on gram. So far, this is working fine. The syntax is listed below.
7. txt_Sum is placed in the form footer and is supposed to sum all values in txt_Conv, but I cannot manage to make this work.
8. txt_Wt% is to calculate and return weight percentage of each component based on the calculated value in txt_Conv. This is where I am stuck, too.

Below is the syntax that I placed in the calculated Field in the query that bound to txt_Conv.

Expand|Select|Wrap|Line Numbers
  1.  ConvWT: IIf([Unit]="G",[QTY],IIf([Unit]="KG",[QTY]*1000,IIf([Unit]="OZ",[QTY]*28.35*1000,IIf([Unit]="LB",[QTY]*16*28.35*1000,0)))) 
Below is the syntax in Control Source of txt_Sum that is supposed to calculate the sum of values in txt_Conv. But Access returns #Error in this textbox. As consequence, the txt_Wt% also has #Error shown.

Expand|Select|Wrap|Line Numbers
  1.  = SUM([txt_Conv]) 
I read some posts related to this subject, but I still cannot figure out how sum function works for calculated values.

Please help.
Thanks in advance!
Mar 20 '12 #1

✓ answered by NeoPa

Have you tried the obvious answer :
Expand|Select|Wrap|Line Numbers
  1. =Sum([ConvWT])
PS. Try the following simpler statement for ConvWT :
Expand|Select|Wrap|Line Numbers
  1. ConvWT: [QTY] * Switch([Unit]='G',1,[Unit]='KG',1000,[Unit]='OZ',28350,[Unit]='LB',453600,True,0)

Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Have you tried the obvious answer :
Expand|Select|Wrap|Line Numbers
  1. =Sum([ConvWT])
PS. Try the following simpler statement for ConvWT :
Expand|Select|Wrap|Line Numbers
  1. ConvWT: [QTY] * Switch([Unit]='G',1,[Unit]='KG',1000,[Unit]='OZ',28350,[Unit]='LB',453600,True,0)
Mar 21 '12 #2

P: 68
NeoPa,

Thanks for the quick response. While I was trying to test the code you suggested, I realize that my query is not functioning (not editable). Something changed when I tried to make the sum function works.

I will report back when I figure out what have failed my query.

Thanks,
Joe
Mar 21 '12 #3

P: 68
NeoPA,

You were right. The soluntion of my question was obvious quite simple. The Sum function of calculated field in this case should refer directly to the field in the query instead of the texbox name.

Also, thanks for the tips of the Switch function. I did not know what it does. It certainly make this code cleaner.

Joe
Mar 22 '12 #4

NeoPa
Expert Mod 15k+
P: 31,494
Joe Y:
You were right. The soluntion to my question was obviously quite simple
Actually, this is an issue that confuses many. If you learn it then you will be in a good position to avoid such problems in future.

PS. Another function that often proves worthwile when selecting between multiple options is Index(). I'll let you look that one up in Context-Sensitive Help ;-)
Mar 22 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.