Acos calculation

  • Thread starter Thread starter KAitchison
  • Start date Start date
K

KAitchison

Hi there, Im hoping someone can help me with a bit of a complex
calculation... i'm fairly new to access..

so what i'm trying to accomplish is this:
one text box (txtleach) accepts a value input but the user, on the 'After
Update' event i want another text box (txtvolume) to calculate..

in excel the calculation looks like
=+((((((1.219^2)*(1*(ACOS((1.219-$E15)/1.219))))-((1.219-$E15)*(SQRT((2*1.219*$E15)-($E15^2))))))*9.449)*264.17)*3.54

right now this is what i have in my code based on what i help i have found
online...

Private Sub txtleach_AfterUpdate()
Dim answer As Integer
Dim sqrt As Integer

answer = Application.worksheetfunction.Acos(((1.219 - [txtleach]) /
1.219))
sqrt = Application.worksheetfunction.sqrt(((2 * 1.219 * [txtleach]) -
([txtleach] ^ 2)))

Me.txtvolume = ((((((1.219 ^ 2) * (1 * answer))) - ((1.219 - [txtleach])
* (sqrt)))) * 9.449) * 264.17 * 3.54
End Sub

this code gives me a #Name in txtvolume and when i try to run it i get an
error that says method or data member not found but after trying lots of
different things to change it i still get the same error... can anyone help
with this??

Krista
 
just a bit of an update.. after browsing online even more i found the
suggestion of making a module.. so now i have a module named 'Modacos' like
this:

Public Function Acos(X)
Atn (-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

i'm not really sure if i am using this module right as i really know nothing
about modules....

and i have changed my afterupdate code on txtleach to look this this:

Private Sub txtleach_AfterUpdate()
Me.txtvolume = ((((((1.219 ^ 2) * (1 * (Acos((1.219 - [txtleach]) /
1.219)))) - ((1.219 - [txtleach]) * (Sqr((2 * 1.219 * [txtleach]) -
([txtleach] ^ 2)))))) * 9.449) * 264.17) * 3.54
End Sub

when i put a number in txtleach and press tab.. i am returning a number in
txtvolume but it is way off of what excel was returning..

origional excel formula:
=+((((((1.219^2)*(1*(ACOS((1.219-$E15)/1.219))))-((1.219-$E15)*(SQRT((2*1.219*$E15)-($E15^2))))))*9.449)*264.17)*3.54

can anyone help me sort this out???
thank you so much
 
The issue is that Access doesn't recognize
Application.worksheetfunction.Acos. Each of the versions of VBA are
essentially the same, but slightly different for the application that they
support. So something in Excel VBA (such as the statement) can't be lifted
and pasted into Access VBA, or Word VBA, or Outlook VBA, etc without
modification.

Have you looked to see if there's an equivalent function in Access. There
are times where the functionality exists, but under a different name. What
exactly does the ACOS() function do? (I'm working on grad school stuff and
don't have the time to research it thoroughly at the moment).
 
A function has to return a value accomplished by using the syntax [function
name] = [value] so the function should be modified to...

Public Function Acos(X)
Acos = Atn (-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

Looks like the AfterUpdate event should work. However, given the complexity
of the statement I would spin it all out to a function as in...

Public Function GetValue(x)

Dim ACOSValue as Double

ACOSValue = Atn (-X / Sqr(-X * X + 1)) + 2 * Atn(1)

GetValue = ((((((1.219 ^ 2) * (1 * (ACOSValue)) - ((1.219 - X) * (Sqr((2 *
1.219 * X) - (X ^ 2)))))) * 9.449) * 264.17) * 3.54

End Function

As you can tell with the multiple nested parenthesis it gets ugly very, very
fast. The idea is to calculate the ACOS value in the first line and then
insert it where you need the value in the big equation. Definately lookat
going this route as you will thoroughly hate yourself if you have to try and
fix the statement in a control - not to mention that it will allow you to
reuse it later.
 
Public Function Acos(X)
Atn (-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

i'm not really sure if i am using this module right as i really know nothing
about modules....
Click to expand...

Your function is almost correct, except you haven't returned a value to
Acos. Try this

Public Function Acos(X) As Double
Acos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function


This should set the value of your function to the calculation. I believe it
would default to 0 otherwise, which would obviously give you a different
answer that what you expect. (for the record, I'm not sure that your acos
calculation in itself is correct... I'll assume it is for now).

Also, be careful of your datatypes. I suspect that an integers will not be
enough. You probably want your fields as Doubles and any variables that you
are using in the calculation as doubles also.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



KAitchison said:
just a bit of an update.. after browsing online even more i found the
suggestion of making a module.. so now i have a module named 'Modacos' like
this:

Public Function Acos(X)
Atn (-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

i'm not really sure if i am using this module right as i really know nothing
about modules....

and i have changed my afterupdate code on txtleach to look this this:

Private Sub txtleach_AfterUpdate()
Me.txtvolume = ((((((1.219 ^ 2) * (1 * (Acos((1.219 - [txtleach]) /
1.219)))) - ((1.219 - [txtleach]) * (Sqr((2 * 1.219 * [txtleach]) -
([txtleach] ^ 2)))))) * 9.449) * 264.17) * 3.54
End Sub

when i put a number in txtleach and press tab.. i am returning a number in
txtvolume but it is way off of what excel was returning..

origional excel formula:
=+((((((1.219^2)*(1*(ACOS((1.219-$E15)/1.219))))-((1.219-$E15)*(SQRT((2*1.219*$E15)-($E15^2))))))*9.449)*264.17)*3.54

can anyone help me sort this out???
thank you so much
Click to expand...
 
Back
Top