Announcement

Collapse
No announcement yet.

MLG Column Total & MLG_Put() Function

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • MLG Column Total & MLG_Put() Function

    Hello,

    I am trying to total a column. It works fine when the values are entered manually. Not so when the values are pasted using the MLG_Put() function. I guess the values pasted are seen as strings.

    Has anyone noticed this? How can this be fixed.

    Kind regards.


    Code:
    Function TotalCredit(MyGrid As Long) As String
      Local tot As Double
      Local x,y As Long
    
      tot = 0
    
      For x = 4 To 4
         For y = 1 To 10
          tot = tot + Val(MLG_Get(myGrid,y,x))
         Next y
      Next x
      
      Control Set Text hJournalEntry, %ID_FRMJOURNALENTRY_LBLSUMCREDIT, Format$(tot, "#,.00")
      
      Function = Format$(tot)
                
    End Function  
     
      Case %MLGN_COMBOCHANGED
           [email protected] 'current row
           [email protected] 'current col    
    
           sDescription = MLG_Get(hJournalGrid, 1, 2)  
           MLG_Put(hJournalGrid,2, 2, sDescription,1)
                            
           dDebit = Val(MLG_Get(hJournalGrid, 1, 3))                  
           MLG_Put(hJournalGrid,2, 4, Format$(dDebit, "#,.00"), 1)
                            
           TotalCredit(hJournalGrid)
    Fredrick Ughimi
    www.meganetsoft.com

  • #2
    Howdy, Fredrick!

    If you'll make a short compilable code example, I'll run it here.

    Comment


    • #3
      Might need to refresh grid after paste?

      DIM s() to fit requested data
      MLG_GetEx hGrid, s()
      Read s()

      Comment


      • #4
        Presuming that the TotalCredit() function is doing the SUM() operation, it could be that a %MLGN_COMBOCHANGED isn't being fired by MLG_PUT() and needs to kicked off separately
        Real programmers use a magnetized needle and a steady hand

        Comment


        • #5
          Hello,

          Thank you all for your response.

          I noticed that pasting with the following statement would give me the correct figure:

          MLG_Put(hJournalGrid, 2, 4, Format$(dDebit), 1)

          While the following statement wouldn't:

          MLG_Put(hJournalGrid,2, 4, Format$(dDebit, "#,.00"), 1)

          Let's say a value of 75,000.00 is read and pasted as 75000 using the first statement, the TotalCredit(hJournalGrid) reads it correctly as 75,000.00

          With the second statement, the value read and pasted is 75,000.00, TotalCredit(hJournalGrid) reads it as 75.00.

          The second statement isn't interpreted correctly.
          Fredrick Ughimi
          www.meganetsoft.com

          Comment


          • #6
            This is off that problem, but I would change Double to Cux in the Total function.
            Single will produce rounding errors and Double might do the same.
            tot = tot + Val(MLG_Get(myGrid,y,x))

            Comment


            • #7
              Instead of using VAL might use String2Long and when tot is finished, tot = tot * .01

              tot = tot + String2long(MLG_Get(myGrid,y,x))

              It will return the correct result if non-numeric characters are in a cell.


              Code:
              MACRO string2money(s) = USING$(",.##",string2long(s)*.01)
              
              FUNCTION PBMAIN( )
               LOCAL s AS STRING
               s = "1,234.56"
               ? FORMAT$(VAL(s))    + $CR +_    '1
                 string2money(s)    + $CR +_    '1,234.56
                 FORMAT$(string2Long(s))        '123456
              END FUNCTION
              
              FUNCTION string2long(snum AS STRING) AS LONG
               IF LEN(sNum) = 0 THEN EXIT FUNCTION
              'use "012345679-" at any position to create long value
              'does not terminate on invalid characters
              'credits to:  john gleason,  paul dixon,  mike trader, mike doty (me)
              'http://www.powerbasic.com/support/pbforums/showthread.php?t=14373i]
              '--------------------------------------------------------------------------------------------
              '3/29/08 Correction:  These 2 lines were somehow missing in previous posting and the code GPF'ed
              '3/17/21              Added IF LEN(sNum) = 0 check
              !mov edi,sNum                   ;get the pointer to the string information (not the string)
              !mov edi,[edi]                  ;get the point to the string contents  (added this)
              '--------------------------------------------------------------------------------------------
              
              !xor edx,edx                    ;the sum =0
              !xor ecx,ecx                    ;the neg flag =0
              !movzx eax,byte ptr [edi]       ;get the first character
              lp:
              !cmp eax,"0"                    ;is character < a "0"?
              !jl  lessthan0                  ;yes, skip to the non-digit check
              !cmp eax,"9"                    ;is it greater than a "9"?
              !jg  nextcharacter              ;yes, get next char.
              'to get here it must be a digit
              !imul edx,10                    ;sum=sum*10 ..
              !add edx,eax                    ;    + digit
              !sub edx,48                     ;    - 48
              !jmp nextcharacter
              lessthan0:
              REM !cmp eax,"."                ;is it a "."           'rem to terminate on decimal
              REM !je done                    ;yes, then exit loop   'rem to terminate on decimal
              !cmp eax,"-"                    ;is it a "-"
              !jne nextcharacter              ;no, get next character
              !mov ecx,1                      ;set the neg flag
              nextcharacter:
              !inc edi                        ;increment the string pointer
              !movzx eax,byte ptr [edi]       ;get next character
              !or eax,eax                     ;test if zero
              !jnz lp                         ;not zero, go back and do next character
              done:
              !cmp ecx,0                      ;is neg flag set?
              !je skip                        ;no, skip next instruction
              !neg edx                        ;yes, negate answer
              skip:
              !mov function,edx               ;write answer to function
              END FUNCTION

              Comment


              • #8
                Hello Mike,

                i did as you advised.

                The issue remains.

                I don't face this issue when I enter values manually.
                Fredrick Ughimi
                www.meganetsoft.com

                Comment


                • #9
                  Thought that would fix it.

                  Originally posted by Gary Beene View Post
                  Howdy, Fredrick!

                  If you'll make a short compilable code example, I'll run it here.

                  Comment


                  • #10
                    Hello Mike,

                    I 'd already replied your post at No6 before I saw No7 (String2long)

                    Using String2long on 75,000.00 it read it as 7,500,000.00.
                    Fredrick Ughimi
                    www.meganetsoft.com

                    Comment


                    • #11
                      That is correct. Final cents/pennies equal 750000000 * .01 = 75000
                      or
                      String2Money(STR$(tot))

                      The String2Money macro formats the string from String2Long:
                      MACRO string2money(s) = USING$(",.##",string2long(s)*.01)

                      Comment


                      • #12
                        Originally posted by Fredrick Ughimi View Post
                        Hello,

                        Thank you all for your response.

                        I noticed that pasting with the following statement would give me the correct figure:

                        MLG_Put(hJournalGrid, 2, 4, Format$(dDebit), 1)

                        While the following statement wouldn't:

                        MLG_Put(hJournalGrid,2, 4, Format$(dDebit, "#,.00"), 1)

                        Let's say a value of 75,000.00 is read and pasted as 75000 using the first statement, the TotalCredit(hJournalGrid) reads it correctly as 75,000.00

                        With the second statement, the value read and pasted is 75,000.00, TotalCredit(hJournalGrid) reads it as 75.00.

                        The second statement isn't interpreted correctly.
                        That look like a classic internationalisation issue. Is the machine in question configured to use the european convention of a comma as the decmial separator?

                        The first statement above uses the system default. Are you sure that it's not showing 75.000,00 and the secoond resulting in 75,0 ?


                        Comment


                        • #13
                          Hello Mike,

                          String2Money(STR$(tot))

                          Perfect! Working as should. Thank you so much.

                          Kind regards.

                          Added: Sorry for the late response. We have been out of electricity for almost two weeks and my electricity generator bailed out on me last night.
                          Fredrick Ughimi
                          www.meganetsoft.com

                          Comment


                          • #14
                            Hello Stuart,

                            Thank you for responding.

                            That look like a classic internationalisation issue. Is the machine in question configured to use the european convention of a comma as the decmial separator?

                            The first statement above uses the system default. Are you sure that it's not showing 75.000,00 and the secoond resulting in 75,0 ?
                            It is 75,000.00. That is what we use in Nigeria. We follow the British when it comes to Date, number formats, currency formats, etc.
                            Fredrick Ughimi
                            www.meganetsoft.com

                            Comment


                            • #15
                              Hi Mike
                              I have modified your post #7 by adding a new string s= "1234567890"

                              but it returns the answer as below

                              Code:
                                        1234567890
                                        12,345,678.62
                                        1234567890
                              so the string2money(s) returns 12,345,678.62 which is wrong?

                              instead of 1,234,567,890.00

                              Comment


                              • #16
                                Good catch!

                                Please try this
                                MACRO string2money(s) = USING$(".##",string2long(s)*.01#)
                                s= "1234567890"
                                12345678.90

                                or


                                MACRO string2money(s) = USING$(".##",string2long(s)/100)


                                Comments on original source of string2long
                                https://forum.powerbasic.com/forum/u...e-code?t=22506

                                Comment


                                • #17
                                  Originally posted by Mike Doty View Post
                                  Good catch!

                                  Please try this
                                  MACRO string2money(s) = USING$(".##",string2long(s)*.01#)
                                  s= "1234567890"
                                  12345678.90

                                  or


                                  MACRO string2money(s) = USING$(".##",string2long(s)/100)
                                  So if no decimal is given the last 2 digits are assumed to be cents?

                                  s = "1,234.56"
                                  1,234.56
                                  s= "1234567890"
                                  12,345,678.90

                                  Comment


                                  • #18
                                    Possible alternative not fully tested
                                    Code:
                                    [COLOR=#0000C0]MACRO[/COLOR] string2money2(s) = [COLOR=#0000C0]USING$[/COLOR]([COLOR=#C020C0]",.##"[/COLOR],[COLOR=#0000C0]VAL[/COLOR]([COLOR=#0000C0]RETAIN$[/COLOR](s, [COLOR=#0000C0]ANY[/COLOR] [COLOR=#C020C0]"0123456789.-"[/COLOR])))

                                    Comment


                                    • #19
                                      Very nice, but definitely different.

                                      All numbers are considered cents with my routine. The decimal has no meaning. 1 =.01, 11 = .11 111 = 1.11
                                      Did this way so there is no floating point math.

                                      My routine treats - sign in any position as a negative result
                                      Your routine will place a leading 0 before a value under a dollar. Some like it.
                                      Your routine does not like multiple . (periods). Actually VAL does not like it.


                                      Code:
                                      MACRO string2money(s) = USING$(".##",string2long(s)/100)
                                      MACRO string2money2(s) = USING$(",.##",VAL(RETAIN$(s, ANY "0123456789.-")))
                                      
                                      FUNCTION PBMAIN( )
                                       LOCAL s AS STRING
                                       s = ".0.1."
                                      
                                       ? FORMAT$(VAL(s))    + $CR +_  [B]  ' 0[/B]
                                         string2money(s)    + $CR +_    '[B].01[/B]
                                         string2money2(s)    + $CR +_   '[B]0.00[/B]
                                         FORMAT$(string2Long(s))        '[B]1[/B]
                                      END FUNCTION
                                      
                                      FUNCTION string2long(snum AS STRING) AS LONG
                                       IF LEN(sNum) = 0 THEN EXIT FUNCTION
                                      'use "012345679-" at any position to create long value
                                      'does not terminate on invalid characters
                                      'credits to:  john gleason,  paul dixon,  mike trader, mike doty (me)
                                      'http://www.powerbasic.com/support/pbforums/showthread.php?t=14373i]
                                      '--------------------------------------------------------------------------------------------
                                      '3/29/08 Correction:  These 2 lines were somehow missing in previous posting and the code GPF'ed
                                      '3/17/21              Added IF LEN(sNum) = 0 check
                                      !mov edi,sNum                   ;get the pointer to the string information (not the string)
                                      !mov edi,[edi]                  ;get the point to the string contents  (added this)
                                      '--------------------------------------------------------------------------------------------
                                      
                                      !xor edx,edx                    ;the sum =0
                                      !xor ecx,ecx                    ;the neg flag =0
                                      !movzx eax,byte ptr [edi]       ;get the first character
                                      lp:
                                      !cmp eax,"0"                    ;is character < a "0"?
                                      !jl  lessthan0                  ;yes, skip to the non-digit check
                                      !cmp eax,"9"                    ;is it greater than a "9"?
                                      !jg  nextcharacter              ;yes, get next char.
                                      'to get here it must be a digit
                                      !imul edx,10                    ;sum=sum*10 ..
                                      !add edx,eax                    ;    + digit
                                      !sub edx,48                     ;    - 48
                                      !jmp nextcharacter
                                      lessthan0:
                                      REM !cmp eax,"."                ;is it a "."           'rem to terminate on decimal
                                      REM !je done                    ;yes, then exit loop   'rem to terminate on decimal
                                      !cmp eax,"-"                    ;is it a "-"
                                      !jne nextcharacter              ;no, get next character
                                      !mov ecx,1                      ;set the neg flag
                                      nextcharacter:
                                      !inc edi                        ;increment the string pointer
                                      !movzx eax,byte ptr [edi]       ;get next character
                                      !or eax,eax                     ;test if zero
                                      !jnz lp                         ;not zero, go back and do next character
                                      done:
                                      !cmp ecx,0                      ;is neg flag set?
                                      !je skip                        ;no, skip next instruction
                                      !neg edx                        ;yes, negate answer
                                      skip:
                                      !mov function,edx               ;write answer to function
                                      END FUNCTION

                                      Comment


                                      • #20
                                        Originally posted by Mike Doty View Post
                                        Very nice, but definitely different.

                                        All numbers are considered cents with my routine. The decimal has no meaning. 1 =.01, 11 = .11 111 = 1.11
                                        Did this way so there is no floating point math.

                                        My routine treats - sign in any position as a negative result
                                        Your routine will place a leading 0 before a value under a dollar. Some like it.
                                        Your routine does not like multiple . (periods). Actually VAL does not like it.

                                        Your routine will place a leading 0 before a value under a dollar. Some like it.

                                        I used the Using$ strait from your post#7
                                        MACRO string2money(s) = USING$(",.##",string2long(s)*.01)

                                        but if you don't want the leading 0 then use same format mask ".##" as in post#19 in both macro's
                                        discard the period in RETAIN$, and assume all values to be cents so divide by 100

                                        So this should be closer
                                        MACRO string2money2(s) = USING$(".##",VAL(RETAIN$(s, ANY "0123456789-"))/100)

                                        as far as "My routine treats - sign in any position as a negative result" I'm curious, what is the logic behind accepting a value like "12-3-456" to mean "-1234.56" ?

                                        In any case I'm just having fun here.

                                        Since "string2long" is converting to LONG any value longer than 10 character long will be wrong IE: "123456789000"
                                        but should be faster since it's ASM code

                                        Here is a way to mimic the - sign anywhere. also values greater than LONG. (limit being what VAL handles)

                                        Code:
                                        [COLOR=#0000C0]MACRO[/COLOR] string2money(s) = [COLOR=#0000C0]USING$[/COLOR]([COLOR=#C020C0]".##"[/COLOR],string2long(s)/[COLOR=#000000]100[/COLOR])
                                        [COLOR=#0000C0]MACRO[/COLOR] string2money2(s) = [COLOR=#0000C0]USING$[/COLOR]([COLOR=#C020C0]".##"[/COLOR],[COLOR=#0000C0]VAL[/COLOR](CleanString(s))/[COLOR=#000000]100[/COLOR])
                                         
                                        [COLOR=#0000C0]FUNCTION PBMAIN[/COLOR]( )
                                         [COLOR=#0000C0]LOCAL[/COLOR] s [COLOR=#0000C0]AS STRING[/COLOR]
                                        [COLOR=#007F00]' s = ".0.1."
                                        ' s = "12-3-456"
                                        ' s = "R12M3yy456[/COLOR]
                                         s = [COLOR=#C020C0]"-123456789000232289"[/COLOR]
                                        [COLOR=#007F00]' s = "-123456789000"
                                        ' s = "123456789000"[/COLOR]
                                         [COLOR=#0000C0]? FORMAT$[/COLOR]([COLOR=#0000C0]VAL[/COLOR](s))    + [COLOR=#0000C0]$CR[/COLOR] +_    [COLOR=#007F00]' 0[/COLOR]
                                           string2money(s)    + [COLOR=#0000C0]$CR[/COLOR] +_    [COLOR=#007F00]'.01[/COLOR]
                                           string2money2(s)    + [COLOR=#0000C0]$CR[/COLOR] +_   [COLOR=#007F00]'0.00[/COLOR]
                                           [COLOR=#0000C0]FORMAT$[/COLOR](string2Long(s))        [COLOR=#007F00]'1[/COLOR]
                                        [COLOR=#0000C0]END FUNCTION[/COLOR]
                                         
                                        [COLOR=#0000C0]FUNCTION[/COLOR] CleanString(s [COLOR=#0000C0]AS STRING[/COLOR]) [COLOR=#0000C0]AS STRING[/COLOR]
                                                [COLOR=#0000C0]IF TALLY[/COLOR] (s,[COLOR=#C020C0]"-"[/COLOR]) =[COLOR=#000000]0[/COLOR] [COLOR=#0000C0]THEN[/COLOR]
                                                     [COLOR=#0000C0]FUNCTION[/COLOR] = [COLOR=#0000C0]RETAIN$[/COLOR](s, [COLOR=#0000C0]ANY[/COLOR] [COLOR=#C020C0]"0123456789"[/COLOR])
                                                [COLOR=#0000C0]ELSE[/COLOR]
                                                     [COLOR=#0000C0]FUNCTION[/COLOR] = [COLOR=#C020C0]"-"[/COLOR] + [COLOR=#0000C0]RETAIN$[/COLOR](s, [COLOR=#0000C0]ANY[/COLOR] [COLOR=#C020C0]"0123456789"[/COLOR])
                                                [COLOR=#0000C0]END IF[/COLOR]
                                        [COLOR=#0000C0]END FUNCTION[/COLOR]
                                         
                                        [COLOR=#0000C0]FUNCTION[/COLOR] string2long(snum [COLOR=#0000C0]AS STRING[/COLOR]) [COLOR=#0000C0]AS LONG[/COLOR]
                                         [COLOR=#0000C0]IF LEN[/COLOR](sNum) = [COLOR=#000000]0[/COLOR] [COLOR=#0000C0]THEN EXIT FUNCTION[/COLOR]
                                        [COLOR=#007F00]'use "012345679-" at any position to create long value
                                        'does not terminate on invalid characters
                                        'credits to:  john gleason,  paul dixon,  mike trader, mike doty (me)
                                        'http://www.powerbasic.com/support/pbforums/showthread.php?t=14373i]
                                        '--------------------------------------------------------------------------------------------
                                        '3/29/08 Correction:  These 2 lines were somehow missing in previous posting and the code GPF'ed
                                        '3/17/21              Added IF LEN(sNum) = 0 check[/COLOR]
                                        [COLOR=#C00000]!mov edi,sNum                   [/COLOR][COLOR=#007F00];get the pointer to the string information (not the string)[/COLOR]
                                        [COLOR=#C00000]!mov edi,[edi]                  [/COLOR][COLOR=#007F00];get the point to the string contents  (added this)[/COLOR]
                                        [COLOR=#007F00]'--------------------------------------------------------------------------------------------[/COLOR]
                                         
                                        [COLOR=#C00000]!xor edx,edx                    [/COLOR][COLOR=#007F00];the sum =0[/COLOR]
                                        [COLOR=#C00000]!xor ecx,ecx                    [/COLOR][COLOR=#007F00];the neg flag =0[/COLOR]
                                        [COLOR=#C00000]!movzx eax,byte ptr [edi]       [/COLOR][COLOR=#007F00];get the first character[/COLOR]
                                        lp:
                                        [COLOR=#C00000]!cmp eax,"0"                    [/COLOR][COLOR=#007F00];is character < a "0"?[/COLOR]
                                        [COLOR=#C00000]!jl  lessthan0                  [/COLOR][COLOR=#007F00];yes, skip to the non-digit check[/COLOR]
                                        [COLOR=#C00000]!cmp eax,"9"                    [/COLOR][COLOR=#007F00];is it greater than a "9"?[/COLOR]
                                        [COLOR=#C00000]!jg  nextcharacter              [/COLOR][COLOR=#007F00];yes, get next char.[/COLOR]
                                        [COLOR=#007F00]'to get here it must be a digit[/COLOR]
                                        [COLOR=#C00000]!imul edx,10                    [/COLOR][COLOR=#007F00];sum=sum*10 ..[/COLOR]
                                        [COLOR=#C00000]!add edx,eax                    [/COLOR][COLOR=#007F00];    + digit[/COLOR]
                                        [COLOR=#C00000]!sub edx,48                     [/COLOR][COLOR=#007F00];    - 48[/COLOR]
                                        [COLOR=#C00000]!jmp nextcharacter[/COLOR]
                                        lessthan0:
                                        [COLOR=#007F00]REM !cmp eax,"."                ;is it a "."           'rem to terminate on decimal
                                        REM !je done                    ;yes, then exit loop   'rem to terminate on decimal[/COLOR]
                                        [COLOR=#C00000]!cmp eax,"-"                    [/COLOR][COLOR=#007F00];is it a "-"[/COLOR]
                                        [COLOR=#C00000]!jne nextcharacter              [/COLOR][COLOR=#007F00];no, get next character[/COLOR]
                                        [COLOR=#C00000]!mov ecx,1                      [/COLOR][COLOR=#007F00];set the neg flag[/COLOR]
                                        nextcharacter:
                                        [COLOR=#C00000]!inc edi                        [/COLOR][COLOR=#007F00];increment the string pointer[/COLOR]
                                        [COLOR=#C00000]!movzx eax,byte ptr [edi]       [/COLOR][COLOR=#007F00];get next character[/COLOR]
                                        [COLOR=#C00000]!or eax,eax                     [/COLOR][COLOR=#007F00];test if zero[/COLOR]
                                        [COLOR=#C00000]!jnz lp                         [/COLOR][COLOR=#007F00];not zero, go back and do next character[/COLOR]
                                        done:
                                        [COLOR=#C00000]!cmp ecx,0                      [/COLOR][COLOR=#007F00];is neg flag set?[/COLOR]
                                        [COLOR=#C00000]!je skip                        [/COLOR][COLOR=#007F00];no, skip next instruction[/COLOR]
                                        [COLOR=#C00000]!neg edx                        [/COLOR][COLOR=#007F00];yes, negate answer[/COLOR]
                                        skip:
                                        [COLOR=#C00000]!mov function,edx               [/COLOR][COLOR=#007F00];write answer to function[/COLOR]
                                        [COLOR=#0000C0]END FUNCTION[/COLOR]

                                        Comment

                                        Working...
                                        X