Results 1 to 15 of 20
Thread: Math problem (Access 2002 XP)

20060823, 15:23 #1
 Join Date
 Feb 2003
 Location
 Piscataway, New Jersey, USA
 Posts
 171
 Thanks
 0
 Thanked 0 Times in 0 Posts
Math problem (Access 2002 XP)
Hi everybody:
This is too easy not to work, but here goes!
I'm doing some simple math in VBA code using doubles. I have:
dblResult = dblCalc ^ dblPower
where (in this case) dblCalc is a small negative number (0.121) and dblPower is a small positive number (0.049).
If I type these values in the Immediate window, I get the answer:
?0.121 ^ 0.049
0.901688401093072
But when I type the calculation using the variables, I get "Runtime error 5: Invalid procedure call or argument."
?dblcalc
0.121
?dblpower
0.049
?dblcalc ^ dblpower
If I try doing this in sql, the cell has "error" in it. Sometimes, I get a different error in sql: "access automation error."
Any idea what is going on?
Thanks!

20060823, 15:31 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Math problem (Access 2002 XP)
The result in the immediate window is wrong. The outcome of 0.121 ^ 0.049 is not a real number (it is a complex number), so the error message you get is correct.

20060823, 17:19 #3
 Join Date
 Feb 2003
 Location
 Piscataway, New Jersey, USA
 Posts
 171
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math problem (Access 2002 XP)
Hi Hans:
Could you please explain that? (I am not a mathematician, "just" a programmer.) I thought the "wrong" answer in the Immediate window looked correct. It came out to a little over 9%.
Also, how should I go about getting the "real" answer? I am trying to calculate the annualized average return of a fund as a percent. Basically, it goes:
dblCalc = ((1+Nz(mo1))*(1+Nz(mo2))*(1+Nz(mo3)).......
dblPower = TotalMonths/12
dblResult (dblCalc ^ dblPower) 1
This works most of the time, but the numbers are small due to being percentages expressed as decimals.
When I do it in Excel's UI, it works okay (each cell holds the average cumulative return for the year as a percent, i.e. 0.1014 = 10.14%):
=((1+B2)*(1+C2)*(1+D2)*(1+E2)*(1+F2)*(1+G2)*(1+H2) *(1+I2)*(1+J2)*(1+K2)*(1+L2)*(1+M2)*(1+N2)*(1+O2)* (1+P2)*(1+Q2)*(1+R2)*(1+S2)*(1+T2)*(1+U2)*(1+V2))^ (12/246)1
but, of course, this is an Access app. How should I do the calc in vba?

20060823, 17:53 #4
 Join Date
 Mar 2004
 Location
 Cincinnati, Ohio, USA
 Posts
 102
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math problem (Access 2002 XP)
Kathryn:
Have you looked at the VBA Financial Functions? My understanding of average rates of return is that my interest is less than it should be. You might be able to use one of the VBA functions rather than possibly reinventing the wheel.

20060823, 18:16 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Math problem (Access 2002 XP)
In addition to FlyerMike's replies (thanks, Mike!), it may be useful to know that many of Excel's financial functions are built into VBA. I'm the opposite of a financial expert (Lounge pay is a very stable and predictable amount: zero), so I cannot really help here, but perhaps you can use the IRR or MIRR functions.
BTW, the reason that you can calculate 0.121 ^ 0.049 in the Immediate window is that it is interpreted as (0.121 ^ 0.049). If you try to calculate (0.121) ^ 0.049, you'll get the same error message you got using variables.

20060823, 18:30 #6
 Join Date
 Mar 2004
 Location
 Cincinnati, Ohio, USA
 Posts
 102
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math problem (Access 2002 XP)
The immediate window doesn't exactly handle this particular math problem well, or accurately.
For example, 4 to the power of 0.5 is really asking for the square root of 4 > 2. I heard a "duh" from the peanut gallery.
In the immediate window,
? 4 ^ 0.5
2
So far, so good.
? (4 ^ 0.5)
2 ...bzzzt.
You can't get the square of a negative number.....it is, as Hans stated, an imaginary number. (2 * i), where "i" is the square root of 1.
The same explanation holds for some negative number raised to the power of a number less than 1.

20060823, 18:33 #7
 Join Date
 Feb 2003
 Location
 Piscataway, New Jersey, USA
 Posts
 171
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math problem (Access 2002 XP)
Hi Hans and FlyerMike:
Thank you for your explanations, which went right over my head. My math career ended in high school in the middle of analytic geometry, when the sexism was so thick you could cut it with a knife, and we girls made a quick escape. That was 1963. Now, I just write code.
Can either of you math whizzes answer my question: how can I get this calc (which works fine in Excel) to work in Access VBA? Unless you're telling me that Excel is making an error and not alerting me?
Still clueless,

20060823, 18:44 #8
 Join Date
 Mar 2004
 Location
 Cincinnati, Ohio, USA
 Posts
 102
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math problem (Access 2002 XP)
Would you mind posting your Excel file, with any confidential data removed? The attachment needs to be less than 100 kb.
http://www.wopr.com/cgibin/w3t/faq_...l?Cat=#attach"
Rules for attachments
BTW, I was thrown out of math classes in the '70s.

20060823, 19:12 #9
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math problem (Access 2002 XP)
It doesn't work in Excel for me.
Legare Coleman

20060823, 19:16 #10
 Join Date
 Mar 2004
 Location
 Cincinnati, Ohio, USA
 Posts
 102
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math problem (Access 2002 XP)
Kathryn:
Here's my best guess. Attached is an Excel file that appears to mimic your explanation. I also included an Access file with a small table and a function appropriately titled "HopeThisWorks". As you will see I built a table of dates (months) and interest rates. I made no attempt to validate that interest rates will be between 0 and 1.
The following function is in the module:
<pre>Public Function HopeThisWorks() As Double
On Error GoTo Err_HopeThisWorks
Dim dMonths As Double, dSubtotal As Double, dResult As Double
Dim MyRS As DAO.Recordset
Set MyRS = CurrentDb.OpenRecordset("tbl_Data")
dSubtotal = 1
'Gather the data points
'For each new piece of data, add 1 to it and multiply by the dSubtotal
With MyRS
If .RecordCount Then
.MoveFirst
While Not .EOF
If Nz(.Fields("MyMonth").Value, 0) Then
dMonths = dMonths + 1
dSubtotal = dSubtotal * (1 + Nz(.Fields("InterestRate").Value, 0))
End If
.MoveNext
Wend
End If
.Close
End With
If dMonths Then
dResult = (dMonths / 12) ^ dSubtotal  1
End If
Exit_HopeThisWorks:
Set MyRS = Nothing
HopeThisWorks = dResult
Exit Function
Err_HopeThisWorks:
MsgBox "Error in HopeThisWorks routine" & VBA.vbNewLine & _
"Error: " & Err.Number & "  " & Err.Description
Resume Exit_HopeThisWorks
End Function
</pre>

20060823, 19:38 #11
 Join Date
 Feb 2003
 Location
 Piscataway, New Jersey, USA
 Posts
 171
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math problem (Access 2002 XP)
okay, here it is:

20060823, 19:40 #12
 Join Date
 Feb 2003
 Location
 Piscataway, New Jersey, USA
 Posts
 171
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math problem (Access 2002 XP)
Hi Hans:
This looks right. It looks like what I'm doing. I'm going to take a break and will test it tomorrow. Meanwhile, I'm checking to ensure the data is valid . . . you know how that goes. GIGO.

20060823, 19:43 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Math problem (Access 2002 XP)
<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Psst  Mike is the one who posted the demo and the code, not I !

20060823, 19:49 #14
 Join Date
 Mar 2004
 Location
 Cincinnati, Ohio, USA
 Posts
 102
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math problem (Access 2002 XP)
I understand the (1+x1)*(1+x2)*(1+x3)...... part
What does the (12/246) exponent mean? In my function I take the number of data points and divide by 12. I thought it was to annualize the months....or something like that.

20060823, 19:52 #15
 Join Date
 Mar 2004
 Location
 Cincinnati, Ohio, USA
 Posts
 102
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Math problem (Access 2002 XP)
Pffft. After I saw your spiral code on the other board, you can have credit for this one too.
<img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>