Results 1 to 15 of 18

100504, 09:19 #1Registered User
 Join Date
 Jul 2004
 Location
 Inverurie
 Posts
 628
Unanswered: Converting an Excel formula into a form that Access can process...
Ok, here's my problem:
I have built a form called front_wheel_build. and i need to convert the following formula from Excel into something that access can deal with:
=SQRT((dL/2*SIN(2*PI()*Cross/(N/2)))^2+(ERD/2((dL/2)*COS(2*PI()*Cross/(N/2))))^2+WL_Effective^2)S/2
Now, this formula is one of two that i will be using, but because they are so closely related, once this one works it is a simple process to get the second one to work, which will be:
=SQRT((dR/2*SIN(2*PI()*Cross/(N/2)))^2+(ERD/2((dL/2)*COS(2*PI()*Cross/(N/2))))^2+WR_Effective^2)S/2
I have the following fields that will be used to calculate this formula:
dl, wl, dr, wr, OLD, s, ERD, OSB, ISO, wl_effective and wr_effective
all the fields (except for wl_effective and wr_effective) are populated after the user makes a selection in two combo boxes. From there, the wl_effective and the wr_effective are calculated using the equations wlOSB and wrOSB respectively (which i have working).
What i need though, is help in calculating the above formula. Im not sure if it is possible by using an equation, or it if have to calculate each individual element first in VB then apply the Square Root to it.
If anyone can give me some assistance with this I would really appreciate it as it's the last thing i have to do to complete the functionality of this part of the database.

100504, 10:06 #2Registered User
 Join Date
 Jul 2004
 Location
 Inverurie
 Posts
 628
I have tried using the following, but i don't seem to get an output at all:
Code:Function mysqr() Dim mysqr mysqr = Sqr((dl.Value / 2 * Sin(2 * 3.14 * Combo39.Value / (combo29.Value / 2))) ^ 2 + (ERD.Value / 2  ((dl.Value / 2) * Cos(2 * 3.14 * Combo39.Value / (combo29.Value / 2)))) ^ 2 + wl_effective.Value ^ 2)  s.Value / 2 End Function

100504, 10:08 #3Registered User
 Join Date
 Jun 2004
 Location
 Terrapin Nation
 Posts
 205
you should create a function to perform the calculation. Something like:
Public ComplicatedFunction(dl as double, wl as double, dr as double, wr as double, OLD as double, s as double, ERD as double , OSB as double, ISO as double, wl_effective as double ,wr_effective as double) as double
ComplicatedFunction = SQR((dR/2*SIN(2*PI()*Cross/(N/2)))^2+(ERD/2((dL/2)*COS(2*PI()*Cross/(N/2))))^2+WR_Effective^2)S/2
End function
Then you could call the function once all fields are completed on your form:
ComplicatedFunction(Forms![yourform].YourControl1,Forms![yourform].YourControl2,Forms![yourform].YourControl3..........)
EtcEtc

100504, 10:10 #4Registered User
 Join Date
 Jul 2004
 Location
 Inverurie
 Posts
 628
Thanks for that TerpInMD. Any ideas on how to refresh a text box with the result once a command button has been clicked?

100504, 10:15 #5Registered User
 Join Date
 Jul 2004
 Location
 Inverurie
 Posts
 628
and next point, Cross, and N are values that the user selected in a couple of combo boxes. Is there any way that i can get the formula to lookup these values?

100504, 10:20 #6Registered User
 Join Date
 Jun 2004
 Location
 Terrapin Nation
 Posts
 205
To "refresh" a text box with the value you would put code like this behind the button whcih calculates is:
YourTextBox = ComplicatedFunctions(List of parameters here)
It that easy.

100504, 10:23 #7Registered User
 Join Date
 Jul 2004
 Location
 Inverurie
 Posts
 628
and next point, Cross, and N are values that the user selected in a couple of combo boxes. Is there any way that i can get the formula to lookup these values?

100504, 10:28 #8Registered User
 Join Date
 Jun 2004
 Location
 Terrapin Nation
 Posts
 205
I answered that in the original reply. Let me give you an easier example:
If you have a combobox and you want to reference it you do so like:
Forms![yourformname].thecontrolname
so:
ComplicatedFormula(Forms![YourFormName].thecontrolname, .........
So Forms![YourFormName].thecontrolname would be the combobox with the value of N in it for example.

100504, 10:35 #9Registered User
 Join Date
 Jul 2004
 Location
 Inverurie
 Posts
 628
ok, im getting an error message when using your code. on the Public calculate(dl as double, wl as double, dr as double, wr as double, OLD as double, s as double, ERD as double , OSB as double, ISO as double, wl_effective as double ,wr_effective as double, combo39 as double, combo29 as double) line, it's highlighting the first As and saying "Syntax Error".
I have attached the database (an Access 97 one) so that you can see this message yourself.

100504, 10:40 #10Registered User
 Join Date
 Jun 2004
 Location
 Terrapin Nation
 Posts
 205
Sorry I cant do that. I can tell just by the fact that you have combo39 as double that you dont quite get the idea of UDF's.

100504, 10:59 #11Registered User
 Join Date
 Oct 2004
 Posts
 8
change
Code:public calculate
Code:public function calculate

100504, 11:14 #12Registered User
 Join Date
 Jul 2004
 Location
 Inverurie
 Posts
 628
Thanks, that helped.
ok, can you suggest the best method for the following:
Once the Calculate button is clicked function calculate left and calculate right are computed, and Textbox 97 and Textbox100 should be updated to show the results.

100504, 11:59 #13Registered User
 Join Date
 Jul 2004
 Location
 Inverurie
 Posts
 628
Could someone please help? im totally stuck here.

100504, 12:02 #14Registered User
 Join Date
 Jun 2004
 Location
 Terrapin Nation
 Posts
 205
Hey, this is the third time I am anserwing your question. Say you have a text box called txtMyTextbox and you want to populate it with "Hello World" after a button is pushed.
On the click event for the button you simply put:
Me.txtMyTextBox = "Hello World"
Now replace "Hellow World" with the result of your function and you are in business.

100504, 12:12 #15Registered User
 Join Date
 Jul 2004
 Location
 Inverurie
 Posts
 628
Sorry, im just getting confused here.
Last edited by cruickshanks; 100504 at 12:26.