Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #12260 > unrolled thread

module for rounding is crashing

Back to article view | Back to comp.databases.ms-access

Contents

  module for rounding is crashing flymo <fly_mo@hotmail.com> - 2016-12-19 10:58 -0800
    Re: module for rounding is crashing Luuk <luuk@invalid.lan> - 2016-12-19 20:28 +0100
    Re: module for rounding is crashing Ron Paii <ron81pai@gmail.com> - 2016-12-19 11:35 -0800
      Re: module for rounding is crashing Luuk <luuk@invalid.lan> - 2016-12-19 20:52 +0100
        Re: module for rounding is crashing Luuk <luuk@invalid.lan> - 2016-12-19 20:57 +0100
    Re: module for rounding is crashing Patrick Finucane <patrickfinucanetx@gmail.com> - 2016-12-19 13:35 -0800
      Re: module for rounding is crashing Ulrich Möller <knobbi38@arcor.de> - 2016-12-19 23:48 +0100
        Re: module for rounding is crashing Patrick Finucane <patrickfinucanetx@gmail.com> - 2016-12-20 06:03 -0800
    Re: module for rounding is crashing flymo <fly_mo@hotmail.com> - 2016-12-19 13:46 -0800
    Re: module for rounding is crashing Jürgen Meyer <juergen.ul.meyer@gmx.de> - 2016-12-20 13:17 +0100
    Re: module for rounding is crashing "Joan Wild" <joanwild@xplornet.com> - 2016-12-20 13:42 +0000
      Re: module for rounding is crashing flymo <fly_mo@hotmail.com> - 2016-12-21 06:05 -0800

#12260 — 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 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

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 strExtraDecimals
    Dim intCents As Integer
    Dim intDecimal As Integer
    Dim strSign As String
    'Turn the money value into a string.
    strMoney = CStr(dblNumber)
    intLength = Len(strMoney)
    If Left(strMoney, 1) = "-" Then
        intLength = intLength - 1
        strMoney = Right(strMoney, intLength)
        strSign = "-"
    Else
        strSign = ""
    End If
    'Find out where the decimal is in the string.
    intDecimal = InStr(1, strMoney, ".")
    If intDecimal = 0 Then
        'There were no decimals to work with.
        MakeMoney = dblNumber
        Exit Function
    End If
    'Get the Dollars to the left of the decimal and turn it into a string.
    intDollar = intDecimal - 1
    strDollar = Left(strMoney, intDollar)
    'Get the Cents to the right of the decimal and turn it into a string.
    intCents = intLength - intDecimal
    strCents = Right(strMoney, intCents)
    If intCents > 2 Then
        strExtraDecimals = Right(strCents, intCents - 2)
        strCents = Left(strCents, 2)
        strExtraDecimals = Left(strExtraDecimals, 1)
        If CInt(strExtraDecimals) > 4 Then
            If CInt(strCents) < 99 Then
                strCents = CStr(CInt(strCents) + 1)
            Else
                strDollar = CStr(CInt(strDollar) + 1)
                strCents = "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 anything.
    MakeMoney = dblNumber
    Exit Function
    
    End If
    If CInt(strCents) < 10 Then
        strCents = "0" + strCents
    End If
        
   'Put the Dollars and 2 digit Cents back together.
    strMoney = strSign + strDollar + "." + strCents
    'Change the String Back into a Double.
    MakeMoney = CDbl(strMoney)
End Function

[toc] | [next] | [standalone]

#12261

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.
> Thanks in advance
> 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)

[toc] | [prev] | [next] | [standalone]

#12262

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



   

[toc] | [prev] | [next] | [standalone]

#12263

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 

[toc] | [prev] | [next] | [standalone]

#12264

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
>> 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
>
>

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 ?????

[toc] | [prev] | [next] | [standalone]

#12265

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 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
> 
> 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 strExtraDecimals
>     Dim intCents As Integer
>     Dim intDecimal As Integer
>     Dim strSign As String
>     'Turn the money value into a string.
>     strMoney = CStr(dblNumber)
>     intLength = Len(strMoney)
>     If Left(strMoney, 1) = "-" Then
>         intLength = intLength - 1
>         strMoney = Right(strMoney, intLength)
>         strSign = "-"
>     Else
>         strSign = ""
>     End If
>     'Find out where the decimal is in the string.
>     intDecimal = InStr(1, strMoney, ".")
>     If intDecimal = 0 Then
>         'There were no decimals to work with.
>         MakeMoney = dblNumber
>         Exit Function
>     End If
>     'Get the Dollars to the left of the decimal and turn it into a string.
>     intDollar = intDecimal - 1
>     strDollar = Left(strMoney, intDollar)
>     'Get the Cents to the right of the decimal and turn it into a string.
>     intCents = intLength - intDecimal
>     strCents = Right(strMoney, intCents)
>     If intCents > 2 Then
>         strExtraDecimals = Right(strCents, intCents - 2)
>         strCents = Left(strCents, 2)
>         strExtraDecimals = Left(strExtraDecimals, 1)
>         If CInt(strExtraDecimals) > 4 Then
>             If CInt(strCents) < 99 Then
>                 strCents = CStr(CInt(strCents) + 1)
>             Else
>                 strDollar = CStr(CInt(strDollar) + 1)
>                 strCents = "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 anything.
>     MakeMoney = dblNumber
>     Exit Function
>     
>     End If
>     If CInt(strCents) < 10 Then
>         strCents = "0" + strCents
>     End If
>         
>    'Put the Dollars and 2 digit Cents back together.
>     strMoney = strSign + strDollar + "." + strCents
>     'Change the String Back into a Double.
>     MakeMoney = CDbl(strMoney)
> End Function

I ran your code on a few numbers.  No errors.  Ran quick. 1213.1999 returned 1213.2.  Seemed all is well.  

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.

 

[toc] | [prev] | [next] | [standalone]

#12267

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

[toc] | [prev] | [next] | [standalone]

#12274

On Monday, December 19, 2016 at 5:47:08 PM UTC-5, Ulrich Möller wrote:
> 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

Hi Ulrich:  I must have copied my numbers incorrectly.  I got the same results as you.

[toc] | [prev] | [next] | [standalone]

#12266

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  

[toc] | [prev] | [next] | [standalone]

#12270

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.
>Thanks in advance
>John
>
Hi John,
Your code runs fine here
(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

[toc] | [prev] | [next] | [standalone]

#12272

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

[toc] | [prev] | [next] | [standalone]

#12275

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.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web