Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628

    Red face 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. #2
    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. #3
    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. #4
    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. #5
    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. #6
    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. #7
    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. #8
    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. #9
    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.
    Attached Files Attached Files

  10. #10
    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. #11
    Join Date
    Oct 2004
    Posts
    8
    change
    Code:
     public calculate
    to
    Code:
     public function calculate
    in your module

  12. #12
    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. #13
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Could someone please help? im totally stuck here.

  14. #14
    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. #15
    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
  •