# Thread: Math problem (Access 2002 XP)

1. ## 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!

2. ## 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.

3. ## 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?

4. ## 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 re-inventing the wheel.

5. ## 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.

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

7. ## 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,

8. ## 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/cgi-bin/w3t/faq_...l?Cat=#attach"

Rules for attachments

BTW, I was thrown out of math classes in the '70s.

9. ## Re: Math problem (Access 2002 XP)

It doesn't work in Excel for me.

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

11. ## Re: Math problem (Access 2002 XP)

okay, here it is:

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

13. ## 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 !

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

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

Page 1 of 2 12 Last

#### Posting Permissions

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