Re: a dynamic query needing a sum function

On 29 Aug, 22:01, OldPro <rrossk...@xxxxxxxxxxxxx> wrote:
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- Hide quoted text -

- Show quoted text -

Thanks for the help! I dont seem to be able to get my head round it!
so a little more info might help. I build the query to show the how
many times the vehicle comes onto the site and how much refuse it has
left, I then open another form frmWeightsTotals using the qry as the
record source. What I want to do is put a control on the forms footer
showing the number of visits and the total weight. it's probably me,
but I tried both your solutions thinking that i might refer to the
first form, but i kept getting syntax errors
Thanks again for the help.
regards
Phil

.

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)