a dynamic query needing a sum function

[Philip_collins]

Hi!
I have a dynamic query that has a form attached. I want to add up a
field with the sum funtion, the field is netkg.

Dim rec As Recordset
Dim qdf As QueryDef
Dim strsql

Set db = CurrentDb
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
If qdf.Name = "qryWeightsTotals" Then
db.QueryDefs.Delete qdf.Name
End If
Next qdf
strsql = "SELECT [Date],[Time],[makemodel],pao, Street,
[town],[Postcode],[gross weight],tare,[NetKG] FROM tblVanDetails WHERE
[RegistrationNo] = '" & zz & "'"
Set qdf = db.CreateQueryDef("qryWeightsTotals", strsql)
db.QueryDefs.Refresh
Me.Refresh
DoCmd.OpenForm "frmweightstotals", acNormal
Set db = Nothing

I have tried replacing [NetKG] with SUM([NetKG]) and SUM([NetKG]) as
TotalNet, this is with TotalNet dimmed as a variant. Have also
attempted, somewhat in ignorance, using the GROUP BY clause after the
where, however this results in a syntax error i.e. does not compile.
The first 2 result in an erroneous error message referring to
aggregating a date (netkg is not a date field), this may mean
something to others more enlghtened than I?!

any pointers would be much appreciated.
Regards
Phil

[OldPro]

On Aug 29, 1:11 pm, [e-mail] wrote:

Hi!
I have a dynamic query that has a form attached. I want to add up a
field with the sum funtion, the field is netkg.

Dim rec As Recordset
Dim qdf As QueryDef
Dim strsql

Set db = CurrentDb
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
If qdf.Name = "qryWeightsTotals" Then
db.QueryDefs.Delete qdf.Name
End If
Next qdf
strsql = "SELECT [Date],[Time],[makemodel],pao, Street,
[town],[Postcode],[gross weight],tare,[NetKG] FROM tblVanDetails WHERE
[RegistrationNo] = '" & zz & "'"
Set qdf = db.CreateQueryDef("qryWeightsTotals", strsql)
db.QueryDefs.Refresh
Me.Refresh
DoCmd.OpenForm "frmweightstotals", acNormal
Set db = Nothing

I have tried replacing [NetKG] with SUM([NetKG]) and SUM([NetKG]) as
TotalNet, this is with TotalNet dimmed as a variant. Have also
attempted, somewhat in ignorance, using the GROUP BY clause after the
where, however this results in a syntax error i.e. does not compile.
The first 2 result in an erroneous error message referring to
aggregating a date (netkg is not a date field), this may mean
something to others more enlghtened than I?!

any pointers would be much appreciated.
Regards
Phil

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 & "'"

[Philip_collins]

On 29 Aug, 19:35, OldPro <[e-mail]:

On Aug 29, 1:11 pm, [e-mail] wrote:

Hi!
I have a dynamic query that has a form attached. I want to add up a
field with the sum funtion, the field is netkg.

Dim rec As Recordset
Dim qdf As QueryDef
Dim strsql

Set db = CurrentDb
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
If qdf.Name = "qryWeightsTotals" Then
db.QueryDefs.Delete qdf.Name
End If
Next qdf
strsql = "SELECT [Date],[Time],[makemodel],pao, Street,
[town],[Postcode],[gross weight],tare,[NetKG] FROM tblVanDetails WHERE
[RegistrationNo] = '" & zz & "'"
Set qdf = db.CreateQueryDef("qryWeightsTotals", strsql)
db.QueryDefs.Refresh
Me.Refresh
DoCmd.OpenForm "frmweightstotals", acNormal
Set db = Nothing

I have tried replacing [NetKG] with SUM([NetKG]) and SUM([NetKG]) as
TotalNet, this is with TotalNet dimmed as a variant. Have also
attempted, somewhat in ignorance, using the GROUP BY clause after the
where, however this results in a syntax error i.e. does not compile.
The first 2 result in an erroneous error message referring to
aggregating a date (netkg is not a date field), this may mean
something to others more enlghtened than I?!

any pointers would be much appreciated.
Regards
Phil

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

[OldPro]

On Aug 29, 3:13 pm, [e-mail] wrote:

On 29 Aug, 19:35, OldPro <[e-mail]:

On Aug 29, 1:11 pm, [e-mail] 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

[Philip_collins]

On 29 Aug, 22:01, OldPro <[e-mail]:

On Aug 29, 3:13 pm, [e-mail] wrote:

On 29 Aug, 19:35, OldPro <[e-mail]:

On Aug 29, 1:11 pm, [e-mail] 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