Results 1 to 15 of 16

052506, 10:06 #1Registered User
 Join Date
 Dec 2005
 Posts
 45
Unanswered: Is there no GoalSeek or Solver in Access?
I need to perform one of these functions in access vba but it doesn't look like they are available.
Does anyone have any ideas?
Thanks!

052506, 12:13 #2Purveyor of Discontent
 Join Date
 Mar 2003
 Location
 The Bottom of The Barrel
 Posts
 6,102
Provided Answers: 1It might help if you included a little blurb on what those functions do...

052506, 12:21 #3Registered User
 Join Date
 Dec 2005
 Posts
 45
Sorry.
Goalseek: Given the formula '15=9x + 12', goalseek will return the value of x that completes the formula. '15' and '=9x+12' are the inputs.
Solver is a more advanced form of goalseek, using multiple inputs and constraints.
I really am just looking for a function or set of code that would duplicate goalseek. I'm guessing a basic loop with comparisons might work.
THanks!

052506, 17:15 #4Purveyor of Discontent
 Join Date
 Mar 2003
 Location
 The Bottom of The Barrel
 Posts
 6,102
Provided Answers: 1What is your practical application for using this function?
There's no inline vba function that will do it for you that I know of. Might be kind of fun to whip one up though...

052506, 20:59 #5(Making Your Life Easy)
 Join Date
 Feb 2004
 Location
 New Zealand
 Posts
 1,407
Provided Answers: 5GoalSeek or Solver
Is that a Excel thing
The only work a round I can come up with is
get msaccess to open excel and full the cells then
get msaccess to read back the answer form the excel filehope this help
See clear as mud
StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB based on my own environment: vb6 sp5
ASP based on my own environment: 5.6
VBNET based on my own environment started 2007
SQL2005 based on my own environment started 2008
MYLE
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

052606, 03:32 #6King of Understatement
 Join Date
 Feb 2004
 Location
 One Flump in One Place
 Posts
 14,912
That's not fun at all myle
HeavyBall  it might be an idea to post your precise formulae (or a good representative sample). I would imagine solving the equation you supplied would be fairly trivial but I have a suspicion that you have much bigger fish to fry....Testimonial:
pootle flump
ur codings are working excelent.

052606, 04:43 #7Registered User
 Join Date
 Apr 2005
 Location
 Zagreb  Croatia
 Posts
 372
Hello HaeavyBall!
If I can include in this matter.
Look at "DemoGoalSeekA2000".
Open Form1, look at Module1.

052606, 09:45 #8Registered User
 Join Date
 Dec 2005
 Posts
 45
Here's what I came up with yesterday. Its an approximation, but seems to do the job. The incremental change and tolerance can be adjusted depending upon need:
Sub test()
Dim x, y As Single
y = 3.22
x = 0
Do
If Abs(Round(2 * (x ^ 2) + x  7, 2)  y) < 0.1 Then
Exit Do
ElseIf 2 * (x ^ 2) + x  7 < y Then
x = x + 0.01
ElseIf 2 * (x ^ 2) + x  7 > y Then
x = x  0.01
End If
Loop
Debug.Print x
End Sub

052606, 10:05 #9Registered User
 Join Date
 Dec 2005
 Posts
 45
I guess this is as much a finance question as anything. We use goalseek in excel to calculate a price given a required return and IRR, and I am just trying to duplicate that calculation in Access.

052606, 11:31 #10Registered User
 Join Date
 Jul 2004
 Location
 Southampton, UK
 Posts
 368
Looks to me like you are trying to solve the quadratic equation:
2 * (x ^ 2) + x  7 = y
The solution for this will have two values for x (because it's a quadratic). You have presented an iterative method for solving such and equation, however, you do not know which solution for x you will get as the answer.
Besides, a quadratic equation can always be solved by formula to give both solutions for x which means you can do all this in a query.
Check out this
Note that your have to use a value of c=(7y) as you need to make the r.h.s. equal zero.
hth
Chris

052606, 22:26 #11Registered User
 Join Date
 Nov 2002
 Location
 Sydney, Australia
 Posts
 11
I feel that Myle's suggest may really be the most practical. Unless you have only one scenario that you need solved  for which you could derive the code  you'd be better served using Excel itself (via code), as this will give you access to all of Excel's functionality.

052906, 11:55 #12Registered User
 Join Date
 Dec 2005
 Posts
 45
Thanks for all the help!
Firstly, I grossly oversimplified what I am trying to do. The best solution for my specific problem would be code that can perform the following with the fewest possible loops. Also, b is a constant, while a is derived from a very long process, so assume a is a random number:
a = 10.254
b = 30
do
if a < b then
a = a + .001
elseif a > b then
a = a  .001
else 'a = b
debug.print a
exit do
end if
loop
The main challenge here being a way to adjust the increment (.001) to minimize loops.Last edited by HeavyBall; 052906 at 12:03.

052906, 12:25 #13Cavalier King Charles
 Join Date
 Dec 2002
 Location
 Préverenges, Switzerland
 Posts
 3,740
does something like the searchinarray idea work?
something like:
c= a/2
do while a<>b
if a>b then
a = ac
else
a = a+c
endif
if (a  b)^2 < c^2 then
c=c/2
endif
loop
as ever  totally untested aircode
izycurrently using SS 2008R2

052906, 12:27 #14Cavalier King Charles
 Join Date
 Dec 2002
 Location
 Préverenges, Switzerland
 Posts
 3,740
or maybe better:
c= a/2
do while a<>b
if a>b then
a = ac
else
a = a+c
endif
if a=b then exit loop
if (a  b)^2 < c^2 then
c=c/2
endif
loopcurrently using SS 2008R2

052906, 18:14 #15Registered User
 Join Date
 Jul 2004
 Location
 Southampton, UK
 Posts
 368
This is similar (but not the same i don't think) as izys suggestion. The point is you half the gap on each iteration
do
if a>b+tolerence or a<btolerence then
a = (a + b)/2
else
debug.print a
exit do
end if
loop
This will get to within 0.01 in about 11 interations using your given example data. I think it will work regardless of the sign of and b.
What kind of formula are you trying to solve ? There are methods for trying to solve different kinds of problems. Are you using a similar formula each time? Is it a polynomial or something more complex ?
hth
Chris