Re: a dynamic query needing a sum function

On Aug 29, 3:13 pm, Philip_coll...@xxxxxxxxxxx wrote:
On 29 Aug, 19:35, OldPro <rrossk...@xxxxxxxxxxxxx> wrote:





On Aug 29, 1:11 pm, Philip_coll...@xxxxxxxxxxx wrote:


First of all, it is bad form to use Date or Time as a field name,
variable name or control name, as these are common functions and
keywords.
Second, DSUM( ) will give you a total based on a SQL query.
Third, SQL can return records or record totals, not both mixed. That
being said, it is possible to have an aggregate sum, where only the
last record would show the total. This is also problematic as Access
doesn't guarantee that any particular record will be the last one.
This will give you a one record total:
strsql = "SELECT SUM([NetKG]) AS NetKG_Total FROM tblVanDetails WHERE
[RegistrationNo] = '" & zz & "'"- Hide quoted text -

- Show quoted text -

Thanks for the response. how do I place that sql within the above code
to get the form to see the total? I'm quite happy to change the field
names!
regards
Phil- Hide quoted text -

- Show quoted text -

It depends on when you want the total to show. If you want it to show
after zz is updated, then put the code in that controls' afterupdate
event. You will want the total control to be a label.
You can use either...

lblTotal.Caption=DSUM("NetKG","tblVanDetails", "[RegistrationNo] = "
& zz )

or ...

Dim cTotal as currency
Dim db as dao.database
Dim rs as dao.recordset
set db=currentdb()
strsql = "SELECT SUM([NetKG]) AS NetKG_Total FROM tblVanDetails
WHERE
[RegistrationNo] = " & zz
set rs=db.openrecordset(strsql,dbopensnapshot)
if rs.eof then
lblTotal.Caption=""
else
lblTotal.Caption=rs!NetKG_Total
endif
rs.close
set rs=nothing
db.close
set db=nothing

.

Relevant Pages

  • RE: Totals Row Not Summing
    ... to SUM, but it doesn't calculate a sum. ... Let's say your subform text box control is txtCost and it's ControlSource is ... I enabled the totals row on the two datasheet subforms, ...
    (microsoft.public.access.forms)
  • Re: Calculation Returns a Null Value
    ... property of the subreport control to return a 0 if there are no records. ... Duane Hookom ... > different subreports. ... > there are totals but no errors then the I should have a 100.00% accuracy. ...
    (microsoft.public.access.reports)
  • Re: Total in footer with VBA
    ... Running Sum on the hidden control. ... > I tried everything to get the totals into an SQL statement or query and it ... the invoice amounts can only be ... >> method of calculating in user-defined functions so you could set control ...
    (microsoft.public.access.reports)
  • Re: Two different totals from one field depending on criteria
    ... It is not unusual for one bad aggregate expression to mess ... the foirm's record source is a query that somehow converted ... Copy/Paste of the week1 text box control source expression ... Now, none of my totals ...
    (microsoft.public.access.formscoding)
  • Re: trouble with SUM function - Repost
    ... calculations in the record source query. ... calculation to be done in the report's record source query. ... Totals query to calculate the group total and then Join the ... I named this control txtProjectTotal. ...
    (microsoft.public.access.reports)