f

#### module for rounding is crashing

Hi Folks
I have a module that I have been using for rounding currency/money for more=
than a decade and usually is problem free. However, since a round of windo=
ws/office update the code is coming up with a Data mismatch error and break=
ing/freezing where ever it is placed.
I  would appreciate any feedback on the code, specifically if it can be upd=
ated/fixed or re-written or is pointing to a problem elsewhere.
John

Code:
Public Function MakeMoney(ByVal dblNumber As Double) As Double
Dim strMoney As String
Dim intLength As Integer
Dim strDollar As String
Dim intDollar As Integer
Dim strCents As String
Dim intCents As Integer
Dim intDecimal As Integer
Dim strSign As String
'Turn the money value into a string.
strMoney =3D CStr(dblNumber)
intLength =3D Len(strMoney)
If Left(strMoney, 1) =3D "-" Then
intLength =3D intLength - 1
strMoney =3D Right(strMoney, intLength)
strSign =3D "-"
Else
strSign =3D ""
End If
'Find out where the decimal is in the string.
intDecimal =3D InStr(1, strMoney, ".")
If intDecimal =3D 0 Then
'There were no decimals to work with.
MakeMoney =3D dblNumber
Exit Function
End If
'Get the Dollars to the left of the decimal and turn it into a string.
intDollar =3D intDecimal - 1
strDollar =3D Left(strMoney, intDollar)
'Get the Cents to the right of the decimal and turn it into a string.
intCents =3D intLength - intDecimal
strCents =3D Right(strMoney, intCents)
If intCents > 2 Then
strExtraDecimals =3D Right(strCents, intCents - 2)
strCents =3D Left(strCents, 2)
If CInt(strCents) < 99 Then
strCents =3D CStr(CInt(strCents) + 1)
Else
strDollar =3D CStr(CInt(strDollar) + 1)
strCents =3D "00"
End If
Else
'Leave the dollars and cents as they were
End If
Else
'There were under 3 deciamal places so you didn't need to do anythi=
ng.
MakeMoney =3D dblNumber
Exit Function
=20
End If
If CInt(strCents) < 10 Then
strCents =3D "0" + strCents
End If
=20
'Put the Dollars and 2 digit Cents back together.
strMoney =3D strSign + strDollar + "." + strCents
'Change the String Back into a Double.
MakeMoney =3D CDbl(strMoney)
End Function
 0
flymo
12/19/2016 6:58:47 PM
comp.databases.ms-access 42670 articles. 0 followers.

11 Replies
956 Views

Similar Articles

[PageSpeed] 18

On 19-12-16 19:58, flymo wrote:
> Hi Folks
> I have a module that I have been using for rounding currency/money for more than a decade and usually is problem free. However, since a round of windows/office update the code is coming up with a Data mismatch error and breaking/freezing where ever it is placed.
> I  would appreciate any feedback on the code, specifically if it can be updated/fixed or re-written or is pointing to a problem elsewhere.
> John
>
> Code:
> Public Function MakeMoney(ByVal dblNumber As Double) As Double

i would create a 'logging' function.
This function should append the value of 'dblNumber' to some textfile

If your program is crashing there can be a need to open the file
directly before appending, and closing it after doing so.

When your program crashed, look at last line of this text.

It should hold the sourcevalue that your function does not like

(my 2 attempts to do something that makes your code fail, failed)

 0
Luuk
12/19/2016 7:28:20 PM
With a quick look, I don't see anything.

Why not use the built in Round() function?

Note Round uses bankers rounding which will not always give the same answer as your function, but should give better results.

example:
Round() rounds up if odd
MakeMoney(1344.975), Round(1344.975)
1344.98              1344.98

Round() rounds down if even
MakeMoney(1344.985), Round(1344.985)
1344.99              1344.98

MakeMoney(1344.995), round(1344.995,2)
1345                 1345

 0
Ron
12/19/2016 7:35:25 PM
On 19-12-16 20:35, Ron Paii wrote:
> With a quick look, I don't see anything.
>
> Why not use the built in Round() function?
>
> Note Round uses bankers rounding which will not always give the same answer as your function, but should give better results.
>
> example:
> Round() rounds up if odd
> MakeMoney(1344.975), Round(1344.975)
> 1344.98              1344.98
>
> Round() rounds down if even
> MakeMoney(1344.985), Round(1344.985)
> 1344.99              1344.98
>
> MakeMoney(1344.995), round(1344.995,2)
> 1345                 1345
>
>
>
>
>

AFAIK the ROUND function needs 2 parameters

and the ROUND function does NOT do bankers rounding

https://support.office.com/en-us/article/ROUND-function-C018C5D8-40FB-4053-90B1-B3E7F61A213C

 0
Luuk
12/19/2016 7:52:03 PM
On 19-12-16 20:52, Luuk wrote:
> On 19-12-16 20:35, Ron Paii wrote:
>> With a quick look, I don't see anything.
>>
>> Why not use the built in Round() function?
>>
>> Note Round uses bankers rounding which will not always give the same
>>
>> example:
>> Round() rounds up if odd
>> MakeMoney(1344.975), Round(1344.975)
>> 1344.98              1344.98
>>
>> Round() rounds down if even
>> MakeMoney(1344.985), Round(1344.985)
>> 1344.99              1344.98
>>
>> MakeMoney(1344.995), round(1344.995,2)
>> 1345                 1345
>>
>>
>>
>>
>>
>
> AFAIK the ROUND function needs 2 parameters
>
> and the ROUND function does NOT do bankers rounding
>
> https://support.office.com/en-us/article/ROUND-function-C018C5D8-40FB-4053-90B1-B3E7F61A213C
>
>

OK, They (Microsoft) tried to define the function ROUND differenly for
MS-Access users ....

https://www.techonthenet.com/access/functions/numeric/round.php

This gives me another clue why i do not like Microsoft, because they try
to re-invent tooo hard ...

--
WHY ?????

 0
Luuk
12/19/2016 7:57:47 PM
On Monday, December 19, 2016 at 1:59:05 PM UTC-5, flymo wrote:
> Hi Folks
> I have a module that I have been using for rounding currency/money for mo=
re than a decade and usually is problem free. However, since a round of win=
dows/office update the code is coming up with a Data mismatch error and bre=
aking/freezing where ever it is placed.
> I  would appreciate any feedback on the code, specifically if it can be u=
pdated/fixed or re-written or is pointing to a problem elsewhere.
> John
>=20
> Code:
> Public Function MakeMoney(ByVal dblNumber As Double) As Double
>     Dim strMoney As String
>     Dim intLength As Integer
>     Dim strDollar As String
>     Dim intDollar As Integer
>     Dim strCents As String
>     Dim intCents As Integer
>     Dim intDecimal As Integer
>     Dim strSign As String
>     'Turn the money value into a string.
>     strMoney =3D CStr(dblNumber)
>     intLength =3D Len(strMoney)
>     If Left(strMoney, 1) =3D "-" Then
>         intLength =3D intLength - 1
>         strMoney =3D Right(strMoney, intLength)
>         strSign =3D "-"
>     Else
>         strSign =3D ""
>     End If
>     'Find out where the decimal is in the string.
>     intDecimal =3D InStr(1, strMoney, ".")
>     If intDecimal =3D 0 Then
>         'There were no decimals to work with.
>         MakeMoney =3D dblNumber
>         Exit Function
>     End If
>     'Get the Dollars to the left of the decimal and turn it into a string=
..
>     intDollar =3D intDecimal - 1
>     strDollar =3D Left(strMoney, intDollar)
>     'Get the Cents to the right of the decimal and turn it into a string.
>     intCents =3D intLength - intDecimal
>     strCents =3D Right(strMoney, intCents)
>     If intCents > 2 Then
>         strExtraDecimals =3D Right(strCents, intCents - 2)
>         strCents =3D Left(strCents, 2)
>         If CInt(strExtraDecimals) > 4 Then
>             If CInt(strCents) < 99 Then
>                 strCents =3D CStr(CInt(strCents) + 1)
>             Else
>                 strDollar =3D CStr(CInt(strDollar) + 1)
>                 strCents =3D "00"
>             End If
>         Else
>             'Leave the dollars and cents as they were
>         End If
>     Else
>         'There were under 3 deciamal places so you didn't need to do anyt=
hing.
>     MakeMoney =3D dblNumber
>     Exit Function
>    =20
>     End If
>     If CInt(strCents) < 10 Then
>         strCents =3D "0" + strCents
>     End If
>        =20
>    'Put the Dollars and 2 digit Cents back together.
>     strMoney =3D strSign + strDollar + "." + strCents
>     'Change the String Back into a Double.
>     MakeMoney =3D CDbl(strMoney)
> End Function

I ran your code on a few numbers.  No errors.  Ran quick. 1213.1999 returne=
d 1213.2.  Seemed all is well. =20

As Ron suggested, round() should work.  I got some weird errors on it b4, i=
t wouldn't round the way I thought if a number was odd.  Ex:
? round(1234.56,2)
1234.57=20
? round(1234.56 + .01,2)
1234.56=20
So you may have to futz it using a kludge.

=20
 0
Patrick
12/19/2016 9:35:19 PM
Thanks for the suggestions.  I will try a logger to see if that pinpoints a specific process or table field.  I just dont understand why it would start failing after such a long time in use...such is life with MS.

I have a rounding function module but it was replaced by the MakeMoney code (I can't remember the actual reason why) and also tried the built in round function, once again no longer used.
The calculations involved usually have a percentage multiplier (which was throwing >2 decimal places and were being inserted into the tables so the code was to manage the insert.
Stepping through the process isn't showing anything obvious...so it continues.
Cheers Guys
 0
flymo
12/19/2016 9:46:29 PM
Hi Patrick,

Am 19.12.2016 um 22:35 schrieb Patrick Finucane:
> As Ron suggested, round() should work.  I got some weird errors on it b4, it wouldn't round the way I thought if a number was odd.  Ex:
> ? round(1234.56,2)
>   1234.57
> ? round(1234.56 + .01,2)
>   1234.56
> So you may have to futz it using a kludge.

my results with access 2010/32bit are a little bit different:

? round (1234.56,2)
1234,56
? round (1234.56 + 0.01,2)
1234,57

Can you please verify your results. On my system the round function in
access seems to be implementing the IEEE-754 standard, also known as
'Round to Even' or 'Banker's Rounding' and it works as expected.

Ulrich

 0
UTF
12/19/2016 10:48:20 PM
On Mon, 19 Dec 2016 10:58:47 -0800 (PST), flymo <fly_mo@hotmail.com> wrote:

>Hi Folks
>I have a module that I have been using for rounding currency/money for more than a decade and usually is problem free. However, since a round of windows/office update the code is coming up with a Data mismatch error and breaking/freezing where ever it is placed.
>I  would appreciate any feedback on the code, specifically if it can be updated/fixed or re-written or is pointing to a problem elsewhere.
>John
>
Hi John,
(Windows 10 / 64 bit, Office 2016 / 32 bit)

To find the problem, you should go to the settings of Access
Extras/Options/General and mark
"Stop at every error"
So Access should show you the line with the problem

Only an annotation
Your code will not work correctly in Germany
intDecimal = InStr(1, strMoney, ".")
Because of the regional settings, in a string the dot becomes a comma
So InStr will never find the separator

To make it international, you can replace it by:
intDecimal = InStr(1, strMoney, ".") Or InStr(1, strMoney, ",")

Juergen
 0
ISO
12/20/2016 12:17:32 PM
Is there a reason you don't just use the CCur() function?

flymo wrote:

> Hi Folks
> I have a module that I have been using for rounding currency/money
> for more than a decade and usually is problem free. However, since a
> round of windows/office update the code is coming up with a Data
> mismatch error and breaking/freezing where ever it is placed.  I
> would appreciate any feedback on the code, specifically if it can be
> updated/fixed or re-written or is pointing to a problem elsewhere.
 0
Joan
12/20/2016 1:42:21 PM
On Monday, December 19, 2016 at 5:47:08 PM UTC-5, Ulrich M=C3=B6ller wrote:
> Hi Patrick,
>=20
> Am 19.12.2016 um 22:35 schrieb Patrick Finucane:
> > As Ron suggested, round() should work.  I got some weird errors on it b=
4, it wouldn't round the way I thought if a number was odd.  Ex:
> > ? round(1234.56,2)
> >   1234.57
> > ? round(1234.56 + .01,2)
> >   1234.56
> > So you may have to futz it using a kludge.
>=20
> my results with access 2010/32bit are a little bit different:
>=20
>     ? round (1234.56,2)
>       1234,56
>     ? round (1234.56 + 0.01,2)
>       1234,57
>=20
> Can you please verify your results. On my system the round function in=20
> access seems to be implementing the IEEE-754 standard, also known as=20
> 'Round to Even' or 'Banker's Rounding' and it works as expected.
>=20
> Ulrich

Hi Ulrich:  I must have copied my numbers incorrectly.  I got the same resu=
lts as you.
 0
Patrick
12/20/2016 2:03:25 PM
On Tuesday, 20 December 2016 08:42:25 UTC-5, Joan Wild  wrote:
> Is there a reason you don't just use the CCur() function?
>
> flymo wrote:
>
> > Hi Folks
> > I have a module that I have been using for rounding currency/money
> > for more than a decade and usually is problem free. However, since a
> > round of windows/office update the code is coming up with a Data
> > mismatch error and breaking/freezing where ever it is placed.  I
> > would appreciate any feedback on the code, specifically if it can be
> > updated/fixed or re-written or is pointing to a problem elsewhere.
> > Thanks in advance John

Hi Joan,
I think at the time, I was having too many issues with built-in functions and the financial nature of the calculations required a standardised method,so I wrote modules to manage it.
 0
flymo
12/21/2016 2:05:07 PM