calculated field - I'm not going to stop asking

[shelspa]

...in hopes of someday solving this three year problem for me that I am convinced has a simple answer. I want to apply a function to five fields in the current record on a field's on enter property. Any function will do: Min, Max, Avg, STDEV, etc. I just can't figure out the syntax. The help file shows how to do it for all records of a field but I need it applied to five fields of the current record. I can do if statements for Avg, but that isn't practical for Min, Max, and STDEV. Here is the one example of code that will not work for me:

Me!Thickness_Std_Deviation.Value = STDEV(Me!Site_1_Thickness.Value, Me!Site_2_Thickness.Value, Me!Site_3_Thickness.Value, Me!Site_4_Thickness.Value, Me!Site_5_Thickness.Value)

That is all one line. It does not like the commas seperating the field names. Anyone with an idea would be a blood brother for life.

 

[romanzero]

do you mean that you want the fields only on the form that you are currently viewing to calculate and not all the fields from the table?

if so, you need to reference the form then the field(s)

 

[shelspa]

That's it. I need the syntax as it pertains to my field names on my form (frmNordiko_5pt) for that.

 

[ajdesalvo]

Create a temp table with one record for each field on the form. Then use the DStDev function (see Access Help).

Create a table called Thickness with one field called Thickness; Number field type, Double type.

Then in your function:

Dim dbs as Database
Dim rst as RecordSet

DoCmd.SetWarnings False
DoCmd.RunSql "Delete * from [Thickness];"
DoCmd.SetWarnings True

set dbs = CurrentDb
set rst = dbs.OpenRecordSet("Thickness&quot

rst.AddNew
rst![Thickness] = Me!Site_1_Thickness
rst.update
rst.AddNew
rst![Thickness] = Me!Site_2_Thickness
rst.update
rst.AddNew
rst![Thickness] = Me!Site_3_Thickness
rst.update
rst.AddNew
rst![Thickness] = Me!Site_4_Thickness
rst.update
rst.AddNew
rst![Thickness] = Me!Site_5_Thickness
rst.Update

rst.close
dbs.close

Me!Thickness_Std_Deviation = DStDev("[Thickness]", "Thickness&quot

Hope this works! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[romanzero]

the way I do it (maybe not the best way but it works) is to open a new macro in order to find the full field reference.

1 open the form (if your form is a subform open the whole
2 then Open a macro
3 Under Action go to "set value"
4 down below you can select the item (the field you want)
click on the ...
5 select the loaded forms
6 select your form
7 select the field you want
8 click OK and then copy the full field reference
9 this is what you need in the VBcode to refer to the field on the form that is open

 

[WhiteZiggy]

One way it write a module on load..


Private Sub Form_Load()

Me.StdDev.SetFocus
Me.StdDev.Text = Me.num1 / Me.num2 / Me.num2 / Me.num2
Me.Field1.setfocus 'focus back to field 1

End Sub



Problem is the functions you want arent supported this way.

This works, but doesnt have the StdDev, etc functions. You would easily build a module using this same logic.

The only hiccup for me was that you must set focus on the form field u wish to update before updating it...


Hope this helps a little..

Jeremy
WZ

 

[shelspa]

Great options to try everyone. I'm running out of time. I'll try them tomarrow.

 

[shelspa]

The temp table solution worked like a charm. I hope the lost hair grows back. Thanks everyone.

 

[ajdesalvo]

Your welcome!

Three years of hair loss, hmmmmmm, I have seen an ad for Avacor, supposed to grow it back!

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[ssecca]

You may want to do it this way

Add the excel reference to the database then you can call the excel function like so

worksheetfunction.StDev(arg1,arg2,arg3,arg4,argn...)

Good Luck
ssecca