Strange problem with equation inside vba module (1 Viewer)

[gstylianou]

Good evening

I am facing a problem which is really strange and I hope someone to give the answer. Within a VBA Module I have several equations some of them are simple and some more complex. Specifically, I have the following equation:

X= 495 / (1.29579 - 0.35004 * (Log(87 + 97 - 32)) + 0.221 * (Log(167))) - 450

The problem is that, it gives a wrong result (completely wrong!!) when I give it as a string in one related field in my form. ! Moreover, the even dumber thing is if i copy and paste it into excel as it is i have the correct result(!!) And I say "correct" since I know exactly the result that should be occur through another Excel file.

Note that:
1. The related field which i want to give the result it is numeric field (Double, Standard, 2)
2. I created a new calculated field in a table with the same equation in order to check what's going wrong with the same data type of field (Double, Standard, 2) and it gives a completely different result from that in VBA

I don't know what else I can say, but the only thing I believe (and I've had it in my mind for hours) maybe there is a problem with my Office package ? - ( 2019 ProPlus 32bit)

Any help is more than welcome

 

[GPGeorge]

Sometimes it helps to provide full details.

In this case two details that would be really helpful are:

The expected result.
The result actually returned.

"completely wrong!!" is a reaction to a problem, not a description of the actual problem.

 

[gemma-the-husky]

I put an = at the front and tried it in excel, and got approx 33.74
but I am not sure how the whole expression is supposed to be calculated

In access, I tried msgbox 495 / (1.29579 - 0.35004 * (Log(87 + 97 - 32)) + 0.221 * (Log(167))) - 450
and got approx 290.67

so I think vba must be parsing it differently.

out of interest might the log be different eg, base 10 in one example, and base e in another.

In access, the denominator (1.29579 - 0.35004 * (Log(87 + 97 - 32)) + 0.221 * (Log(167)))
on its own evaluates to approx 0.668.... so 496/0.668... will be about 750, less 450 gives about 300.

In excel the denominator (1.29579 - 0.35004 * (Log(87 + 97 - 32)) + 0.221 * (Log(167))) gives 1.02...
so 495/1.02 - 450 returns approx 33.74

I am sure the issue is how the denominator should be resolved.

I just checked, and excel does use base 10 as the default, but I think access vba uses base e (2.71828)
using log(nnn,2.71828) (the base is an optional parameter) in your example, I get the same result as access. I couldn't see a constant value for e, so I used 2.71828, but that would introduce small errors in excel, which may be significant

eg in Excel this =495 / (1.29579 - 0.35004 * (LOG(87 + 97 - 32,2.71828)) + 0.221 * (LOG(167,2.71828))) - 450
now gives roughly the same result as access.

 

[gstylianou]

The correct result is 33,7396 in master excel file with absolutely same equation. I access i get 292,677014..!!!

 

[gemma-the-husky]

The correct result is 33,7396 in master excel file with absolutely same equation. I access i get 292,677014..!!!

it's the base of the logarithm causing the problem. See my post #3. Access uses base e
Just to be precise I got 290.677 in access, not 292.677.

 

[CarlettoFed]

If logarithms must be base 10, divide the expression by Log(10)

495 / (1.29579 - 0.35004 * (Log(87 + 97 - 32)/Log(10)) + 0.221 * (Log(167)/Log(10))) - 450

which as a result of 33.7396011841917

 

[gstylianou]

Hi CarlettoFed and thanks. You are right with your modification i get the correct result but,

the original equation is
x = 495 / (1.29579 - 0.35004 * (Log(Wst + Hps - Nec) / Log(10)) + 0.221 * (Log(167) / Log(10))) - 450 which
Wst is a string from field [Waist]=87
Hps is a string from field [Hips]=97 and
Nec is string from field [Neck]=32 but....... unfortunally now I'm getting totally wrong again result (766,546632982755)

 

[gstylianou]

Hi CarlettoFed and thanks. You are right with your modification i get the correct result but,

the original equation is
x = 495 / (1.29579 - 0.35004 * (Log(Wst + Hps - Nec) / Log(10)) + 0.221 * (Log(167) / Log(10))) - 450 which
Wst is a string from field [Waist]=87
Hps is a string from field [Hips]=97 and
Nec is string from field [Neck]=32 but....... unfortunally now I'm getting totally wrong again result (766,546632982755)

if i put directly the numbers of 87 + 97 - 32 the result is correct 33,73

 

[theDBguy]

the original equation is
x = 495 / (1.29579 - 0.35004 * (Log(Wst + Hps - Nec) / Log(10)) + 0.221 * (Log(167) / Log(10))) - 450 which
Wst is a string from field [Waist]=87
Hps is a string from field [Hips]=97 and
Nec is string from field [Neck]=32 but....... unfortunally now I'm getting totally wrong again result (766,546632982755)

Probably because 87+97-32=152; however, "87"+"97"-"32"=8765.

 

[gstylianou]

Probably because 87+97-32=152; however, "87"+"97"-"32"=8765.

Any idea theDBguy

 

[gemma-the-husky]

I don't know. Can you legitimately add those 3 expressions inside the log function?
Log(waist + hips - nec)? Does that make sense logically and mathematically? I just don't understand why you would add the first 2 and deduct the neck.

(As well as what @theDBguy just said)

 

[Pat Hartman]

495 / (1.29579 - 0.35004 * (Log(87 + 97 - 32)) + 0.221 * (Log(167))) - 450
Giving 290.677014223742

There are missing parentheses in the expression and I don't know where they go so I will put them where Access puts them:
495 / (1.29579 - (0.35004 * (Log(87 + 97 - 32))) + (0.221 * (Log(167)))) - 450
Removing the extra parentheses gives us
495 / (1.29579 - (0.35004 * Log(87 + 97 - 32)) + (0.221 * Log(167))) - 450
Which still returns the 290.677 number

I tried adding the parentheses to different parts of the expression and couldn't come close to what you want. I kept getting a negative number. So, I'll leave it to you to figure out how to fix the bracketing. It is hard to believe that Excel is working under different rules but it seems to be.

Access uses the Boolean Logic rules and expressions are evaluated in this order
Negation
Exponentiation
Multiplication
Division
Addition
Subtraction

You need to use parentheses to control order in a complex expression.

All I can say is to break it down as I placed the parentheses and compute each part in both Excel and Access to see where the difference is.
Looks like the logs are different and I don't know why

Log(87+97 - 32) = 2.181843588 in Excel
Log(167) = 2.222716471 in Excel

​ ​

 

[Pat Hartman]

The answer is in the help entry for the Log function

Log Function - Microsoft Support

support.microsoft.com

 

[The_Doc_Man]

OK, to confirm something said by Dave among others:

In Excel, LOG is base 10 because they have an LN function that is base e (2.7182818284590452353602874713527....) However, those are intrinsic GUI functions. I honestly am not sure what functions you would use in Excel's VBA.

Excel Math Functions

For VBA, you get the difference because LOG in Access VBA is base e as well. VBA does not have a base-10 logarithm, though conversion is easy enough.

Log function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

I checked and there is no relevant difference between VBA and Excel in terms of operator precedence. Therefore, when you evaluate the parts, to my understanding of the parsing operation, which does (a) parentheses then (b) functions then (c) multiply/divide then (d) add/subtract:

X= 495 / (1.29579 - 0.35004 * (Log(87 + 97 - 32)) + 0.221 * (Log(167))) - 450

is evaluated (using Windows calculator to entirely too many digits, looks like 32) as:

Part 1: (87+97-32) = 152 (exact)
Part 2: Log( Part 1) = 2.1818435879447725471775501599304 (base 10)
Part 3: 0.35004 * Part 2 = 0.76373252952418818241402965798204
Part 4: 1.29579 - Part 3 = 0.53205747047581181758597034201796
Part 5: Log(167) = 2.2227164711475832799840759099205 (base 10)
Part 6: 0.221 * part 5 = 0.49122034012361590487648077609242
Part 7: Part 4 + Part 6 = 1.0232778105994277224624511181104
Part 8: 495 / Part 7 = 483.73960118419168358323489615831
Part 9: Part 8 - 450 = 33.739601184191683583234896158306

I would suggest that as an experiment, you build a nine-step computation in your context. Remember that to get the common log of X you need to take the natural log of X and divide that by the natural log of 10. So Log(10) of X = log(e) of X / log(e) of 10. Or, in VBA

Log10X = Log(X)/Log(10)

If you do the experiment, you will perhaps find at what step you went wrong. Be nice to yourself in this sense: With VBA, you never get more than about 15-16 digits for a DOUBLE. For the math I did above, the digits are certainly correct for the first 20-25 places but those logarithms can be somewhat tricky to evaluate, even on computers with extended precision.

 

[Pat Hartman]

I think all the OP needs to do once I clarified that it was the Log() function that is causing the difference, is to just use the method provided in the help entry to get the base10 log which is what he wants.

 

[CarlettoFed]

We should see how you recover the three fields and what type they are.

 

[gstylianou]

Good morning,
the problem was solved simply by changing the string fields to Integer as numeric and now it is correct. However, to be honest, I have never encountered anything similar in my 10 years of experience with access

In any case, thanks for the interest

 

[Pat Hartman]

So you didn't change the calculation to use a base 10 log instead of the natural log??????

 

[gstylianou]

Yes, i changed them as following
x = 495 / (1.29579 - 0.35004 * (Log(Wst + Hps - Nec) / Log(10)) + 0.221 * (Log(167) / Log(10))) - 450 which
Wst As Integer [Waist]=87
Hps As Integer [Hips]=97 and
Nec As Integer [Neck]=32

 

[Pat Hartman]

Thanks for posting the solution. Out of curiosity, what is the purpose of the equation?