Reports: Sums

[OldManRiver]

All,

All the help & HOWTOs say you can get subtotal from groupings and grand totals with the use of:

=sum([fieldname])

and I have used this in the past, but current report is erroring by giving the popup, asking for value entry on each of these I placed in the report.

I'm guessing this is a reference or config problem at the Access or Office level.

Does anyone know what is causing this and how to correct it?

Anyway I used the expression builder and pulled it directly from the current report form, so know syntax is not the issue.

So found and followed HOWTO at:

http://office.microsoft.com/en-us/ac...224441033.aspx

and had encountered some problems setting date fields on the report, which I resolved with HOTOW at:

How to assign values to Unbound Text box in Report thru VBA code ? - bytes

using the function set of:

=myfunction()

which worked fine.

Wondering if I have to also do this in the same manner, so looked up help on DSUM, but was not able to make it work right, because that tends to pull sum for entire report, not by the grouping section.

Here is the code I have in the report so far:

Option Compare Database
Public SubTot
Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
'    SBTasn = CliSbT([tboxCLI])
    Call CliSbT([tboxCLI])
'    Me![tboxSBT].ControlSource = CliSbT([tboxCLI])
'    Me![tboxSBT].Requery
End Sub
Private Sub GroupFooter1_Print(Cancel As Integer, PrintCount As Integer)
'    Call CliSbT([tboxCLI])
End Sub
Private Sub Report_Open(Cancel As Integer)
End Sub
Function BegDate() As Date
    BegDate = FrmSDate
End Function
Function EndDate() As Date
    EndDate = FrmEDate
End Function
Function SBTasn() As Date
    SBTasn = CliSbT([tboxCLI])
End Function
Function CliSbT(myClient)
    Dim FMTbeg, FMTend, WHRstr
    FMTbeg = DateSerial(Year(FrmSDate), Month(FrmSDate), Day(FrmSDate))
    FMTend = DateSerial(Year(FrmEDate), Month(FrmEDate), Day(FrmEDate))
    WHRstr = "(([tmp_cnm] = '" & myClient & "')) AND " & _
             "((([tmp_wdt] >= #" & FMTbeg & "# AND [tmp_wdt] <= #" & FMTend & "#)) OR " & _
             "(([tmp_ted] >= #" & FMTbeg & "#) AND ([tmp_ted] <= #" & FMTend & "#)))"
    SubTot = DSum("[tmp_bhr]*[tmp_wir]", "tmpREPfnr", WHRstr)
End Function
Function GrndTot()
'    GrndTot DSum("[tmp_bhr]*[tmp_wir]", "tmpREPfnr",)
End Function

The function I'm working on for the subtotal by client is "CliSbT". Put break on the "End Function" line to check the values and got the right values for each section, but not geting correctly assigned in report so was getting "#Name?" instead, now getting just "0.00".

Any ideas on my dilemma?

OMR

 

[boblarson]

The key is that the control that houses your field can't be named the same. So, if you have a field named CliSbT then your text box that is bound to that field on the report should be named txtCliSbT instead of just CliSbT.

Then you use the

=Sum([CliSbT])

which should give you the sum of that field.

 

[OldManRiver]

B,

I wish it were that simple. I did state that I have successfully used this before, but on this machine, with this config, that just does not work. Even existing "working before" reports blow.

I name all my text boxes "tboxXXX" and the sum box is named "tboxSBT" for SubTotal. The amount field in the detail it is to sum on is named "tboxAMT" for Amount.

Yes originally I tried:

=sum(tboxAMT)
and
=sum([tboxAMT])

both blew, so since the assignment via function was working for dates, tried that.

When I could not make that work, then I opened this thread.

Any more ideas?

OMR

 

[boblarson]

If it works on other computers but not on this one, what version of Access are you using and what service pack are you on for it?