Sum Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the following query to calculate membership fees in a period. The
query is returning an incorrect value of about half the true amount and I
can't work out why. I am using Access 2003 and an mdb database.

strSQL = "SELECT Sum(tblReceipts.ReceiptAmount) AS SumOfReceiptAmount" & _
" FROM (tblContacts INNER JOIN tblReceipts ON tblContacts.ContactID =
tblReceipts.ContactID)" & _
" INNER JOIN tblExtraDetails ON tblContacts.ContactID =
tblExtraDetails.UniqueID" & _
" WHERE ((tblReceipts.DateTime)> #" & varStartDate & "# And
(tblReceipts.DateTime)< #" & varEndDate & "#);"

Set qdf = CurrentDb.CreateQueryDef("", strSQL)
Set rstContact = qdf.OpenRecordset
If Not rstContact.EOF Then
curMFeeThMon = rstContact!sumofreceiptamount

Any help much appreciated
Thanks Paul Mendlesohn
 
Good point, I removed the tblExtraDetails but still getting the wrong result
(same as before)
Thanks
PAul
 
=?Utf-8?B?UGF1bCBNZW5kbGVzb2hu?=
" WHERE ((tblReceipts.DateTime)> #" & varStartDate & "# And
(tblReceipts.DateTime)< #" & varEndDate & "#);"
Click to expand...

You don't say where you are posting from, but this is not a safe way to
pass dates in the USA and not a legal way anywhere else. You really need
to specify a Jet-compatible date format explicitly:

Const sJetDate As String = "\#yyyy\-mm\-dd\#"


jetSQL = "SELECT etc etc " & _
" WHERE tblReceipts.DateTime >" & Format(varStartDate, sJetDate) & _
" AND tblReceipts.DateTime <" & Format(varEndDate, sJetDate)

This line also really really helps:

Debug.Assert vbYes=MsgBox(jetSQL,"vbYesNo","Is This Okay?")

I have a sneaking suspicion that your inequality operators are wrong. You
are excluding receipts that occur on the start date and end date, where
people are generally used to specifying starts and ends inclusively.
Eyeballing the final SQL statement makes it easy to pick up errors like
this.

And finally, I don't really see the point of creating the temporary
querydef: you can just open the recordset directly.

ss = db.OpenRecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)


Hope that helps


Tim F
 
I was to say the same. You probably don't have records in
tblExtraDetails for every contactID (as the table name implies). Since
you don't refer to the extra details, removing the join and the table
makes sense.

If you must include extra details if they exist, you can play with left
joins: double-click the join line in the query grid, and choose the
appropriate direction.
 
Back
Top