Sum of calculated values in datasheet form

[Joe Y]

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 form’s 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.

 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.

 = 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!

[NeoPa]

Have you tried the obvious answer :

=Sum([ConvWT])

PS. Try the following simpler statement for ConvWT :

ConvWT: [QTY] * Switch([Unit]='G',1,[Unit]='KG',1000,[Unit]='OZ',28350,[Unit]='LB',453600,True,0)

[Joe Y]

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

[Joe Y]

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

[NeoPa]

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 ;-)