# Thread: Converting an Excel formula into a form that Access can process...

1. Registered 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 wl-OSB and wr-OSB 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.

2. Registered 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```
Can anyone advise me on how to refresh a piece of code when a button is clicked on?

3. Registered 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

4. Registered 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?

5. Registered 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?

6. Registered 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.

7. Registered 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?

8. Registered 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.

9. Registered 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.

10. Registered 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.

11. Registered User
Join Date
Oct 2004
Posts
8
change
Code:
` public calculate`
to
Code:
` public function calculate`

12. Registered 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.

13. Registered User
Join Date
Jul 2004
Location
Inverurie
Posts
628

14. Registered 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.

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•