a dynamic query needing a sum function

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

.

Relevant Pages

  • Re: Dcount error
    ... Dim strSQL As String ... Dim dbs As Database ... Dim qdf As QueryDef ... Dim qryName As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Find text in SQL
    ... database as a query named something along those lines. ... >> Dim dbs As DAO.Database ... >> Dim qdf As QueryDef ...
    (microsoft.public.access.modulesdaovba)
  • Re: Querydef
    ... Dim qdf As DAO.QueryDef ... Dim fld As DAO.Field ... Set qdf = db.QueryDefs ...
    (microsoft.public.access.modulesdaovba)
  • Re: Querydef
    ... Dim qdf As DAO.QueryDef ... Dim fld As DAO.Field ... Set qdf = db.QueryDefs ...
    (microsoft.public.access.modulesdaovba)
  • Re: Querydef
    ... Dim qdf As DAO.QueryDef ... Dim fld As DAO.Field ... Set qdf = db.QueryDefs ...
    (microsoft.public.access.modulesdaovba)