Writing an expression which Sums textboxes containing running totals (1 Viewer)

JYeoman · Aug 28, 2018

Hi,
I have created a report which uses references from several queries, taking only a specific record depending on which Week number they type in. In one of these queries is a running total using the code:

Code:

RunTot8: DSum("[Rotorua_TotalSales]","[AllData]","[Week Number] <=" & [Week Number])

Where 'Week number' is determined by the result of another query asking for input on the week number.
So
When I openb the report in report view it asks for a week number, and If I type in "8" it will return all the records from week 8, including a running total up to week 8.

The problem is, now my boss has asked me to do a total of ALL records for the week that gets inputted, and when I added a textbox at the bottom of the report with an expression of

Code:

[Text311]+[Text312]+[Text313]

etc, it returns ############################################## in the textbox in report view. I know it's not an issue of textbox width because I can widen it as far as possible and it still just says ##########.

I'm guessing there is a problem with creating a sum of running totals, but I'm not sure where the problem is or how to fix it.

Any help would be much appreciated! :banghead:

EDIT:
I've just discovered that when I show it in report view, it isn't actually adding the numbers together into the textbox, but writing both num,bers side by side. For example:
If [Text311] was $45 and [Text312] was $99, the textbox prints "$45$99"

JHB · Aug 28, 2018

You could use the Val-function.

Code:

[B][COLOR=Red]Val([/COLOR][/B][Text311][B][COLOR=red])[/COLOR][/B]+[B][COLOR=Red]Val([/COLOR][/B][Text312][B][COLOR=red])[/COLOR][/B]+[B][COLOR=Red]Val([/COLOR][/B][Text313][B][COLOR=red])[/COLOR][/B] ...

arnelgp · Aug 28, 2018

you can use:

Code:

=FormatCurrency(Val(Replace([Text311],"$","")) + 
Val(Replace([Text312],"$","")) + 
Val(Replace([Text313],"$","")))

JYeoman · Aug 29, 2018

JHB said:

You could use the Val-function.

Code:

[B][COLOR=Red]Val([/COLOR][/B][Text311][B][COLOR=red])[/COLOR][/B]+[B][COLOR=Red]Val([/COLOR][/B][Text312][B][COLOR=red])[/COLOR][/B]+[B][COLOR=Red]Val([/COLOR][/B][Text313][B][COLOR=red])[/COLOR][/B] ...

This returns $0 instead of adding the numbers =\

JYeoman · Aug 29, 2018

arnelgp said:
you can use:

Code:

=FormatCurrency(Val(Replace([Text311],"$","")) + Val(Replace([Text312],"$","")) + Val(Replace([Text313],"$","")))

This is the first attempt at making this happen that actually gave me a calculated answer! However the calculation it gave me was very very wrong

For example, the running total after 8 records was the following, and the result below:
$386 845.79
$395 254.86
$257 062.66

The calculation gave me an answer of:
$1 038.00
EDIT: now that I look at it closer, that 1 038.00 could be 1.038 million, which is what the 3 added numbers is rounded to (pretty much)

arnelgp · Aug 29, 2018

add more cleaning code:

=FormatCurrency(Val(Replace(Replace([Text311],"$",""), " ", "")) +
Val(Replace(Replace([Text312],"$",""), " ", "")) +
Val(Replace(Replace([Text313],"$",""), " ", "")))

JYeoman · Aug 29, 2018

arnelgp said:
add more cleaning code:

=FormatCurrency(Val(Replace(Replace([Text311],"$",""), " ", "")) +
Val(Replace(Replace([Text312],"$",""), " ", "")) +
Val(Replace(Replace([Text313],"$",""), " ", "")))

Same answer, but I figured that it could possibly be showing the answer as 1 038.00 could be 1.038 million. I added the numbers up by myself and it was roughly 1 039 000 - could be a formatting and rounding issue now =\

arnelgp · Aug 29, 2018

try creating a Public function
in a module:

Code:

Public Function fncStrSum(ParamArray p() As Variant) As Double
const num as string="0123456789."
dim result as double
dim ln as integer
dim newstr as string
dim var as variant
dim i as integer
dim c as string
for each var in p
    ln=len(var & "")
    newstr=""
    for i = 1 to ln
         c=mid(var & "", i, 1)
         if instr(num, c)<>0 then
              newstr=newstr & c
         end if
    next i
    result=result + cdbl(newstr)
next var
fncStrSum=result
end function

use this sample expression on the grand total
textbox:

=fncStrSum([textbox1], [textbox2], [textbox3])

MajP · Aug 29, 2018

It is not a formatting or rounding issue. It is a limitation of the val function. This can be tested by
?val("386,845.79")
386
That is the reason you have to replace the commas and the $ sign.

From MS

Val( string ) The required stringargument is any valid string expression. Remarks. The Val function stops reading the string at the first character it can't recognize as part of a number. Symbols and characters that are often considered parts of numeric values, such as dollar signs and commas, are not recognized.

JYeoman · Aug 29, 2018

arnelgp said:

try creating a Public function
in a module:

Code:

Public Function fncStrSum(ParamArray p() As Variant) As Double
const num as string="0123456789."
dim result as double
dim ln as integer
dim newstr as string
dim var as variant
dim i as integer
dim c as string
for each var in p
    ln=len(var & "")
    newstr=""
    for i = 1 to ln
         c=mid(var & "", i, 1)
         if instr(num, c)<>0 then
              newstr=newstr & c
         end if
    next i
    result=result + cdbl(newstr)
next var
fncStrSum=result
end function

use this sample expression on the grand total
textbox:

=fncStrSum([textbox1], [textbox2], [textbox3])

You BEAUTIFUL person

MajP · Aug 29, 2018

FYI,
This would simply work as well

Code:

=CCur([text1]) + CCur([Text2]) + CCur([text3])

Test

Code:

?ccur("$123,456.78") + ccur("$456,789.10")
 580245.88

Does not have the limitations of the val function

JYeoman · Aug 29, 2018

arnelgp said:

try creating a Public function
in a module:

Code:

Public Function fncStrSum(ParamArray p() As Variant) As Double
const num as string="0123456789."
dim result as double
dim ln as integer
dim newstr as string
dim var as variant
dim i as integer
dim c as string
for each var in p
    ln=len(var & "")
    newstr=""
    for i = 1 to ln
         c=mid(var & "", i, 1)
         if instr(num, c)<>0 then
              newstr=newstr & c
         end if
    next i
    result=result + cdbl(newstr)
next var
fncStrSum=result
end function

use this sample expression on the grand total
textbox:

=fncStrSum([textbox1], [textbox2], [textbox3])

ok, so I tried to add in the othr 9 fields to sum up into that expression builder, and received a 'mismatch' error, highlighting the code:

Code:

result = result + CDbl(newstr)

I then erased it back to the 3 textboxes to add, and now it keeps showing that error

MajP · Aug 29, 2018

How about simply

Code:

Public Function GetTotal(ParamArray Vals() As Variant) As Currency
  Dim i As Integer
  For i = 0 To UBound(Vals)
     If Trim(Vals(i) & " ") = "" Then Vals(i) = 0
     GetTotal = GetTotal + CCur(Vals(i) & "")
  Next i
End Function

worked with commas, $, Nulls, and ""

MajP · Aug 29, 2018

If you want a little more safety

Code:

Public Function GetTotal(ParamArray Vals() As Variant) As Currency
  Dim i As Integer
  For i = 0 To UBound(Vals)
     If Trim(Vals(i) & " ") = "" Then Vals(i) = 0
     If IsNumeric(Vals(i)) Then
       GetTotal = GetTotal + CCur(Vals(i) & "")
     End If
  Next i
End Function

Test

Code:

?GetTotal ("",Null,"$100","24,000","ABC","25.67")
 24125.67

Empty, Null, $,comma,Text, decimal

JYeoman · Aug 29, 2018

MajP said:
FYI,
This would simply work as well

Code:

=CCur([text1]) + CCur([Text2]) + CCur([text3])

Test

Code:

?ccur("$123,456.78") + ccur("$456,789.10") 580245.88

Does not have the limitations of the val function

Actually this worked perfectly. Very simple, thanks!

Writing an expression which Sums textboxes containing running totals (1 Viewer)

JYeoman

Registered User.

JHB

Have been here a while

arnelgp

..forever waiting... waiting for jellybean!

JYeoman

Registered User.

JYeoman

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

JYeoman

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

MajP

You've got your good things, and you've got mine.

JYeoman

Registered User.

MajP

You've got your good things, and you've got mine.

JYeoman

Registered User.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

JYeoman

Registered User.

Similar threads

Users who are viewing this thread