operations on a recordset

  • Thread starter Thread starter dickminter
  • Start date Start date
D

dickminter

Is there a function to return the sum the values of a
recordset column (such as the CSUM function in
ObjectPal)? Apply a filter to a recordset (other than by
opening the recordset on a query)?

DM
 
Is there a function to return the sum the values of a
recordset column
Click to expand...

SELECT SUM(MyField) AS FieldTotal
FROM MyTable
WHERE SomethingBad = False;

or in groups

SELECT Heading,
SUM(MyField) AS FieldTotal
FROM MyTable
GROUP BY Heading
WHERE SomethingBad = False
ORDER BY Heading DESC;
Apply a filter to a recordset (other than by
opening the recordset on a query)?
Click to expand...
Don't understand. The filter _is_ the recordset. You can filter a query by
something like

SELECT This, That, TheOther
FROM qryMyQueryDef
WHERE SomethingBad = TRUE
ORDER BY TheOther

Hope that helps


Tim F
 
Sure, you can use DSUM


MyVaraible = dsum("PayAmount","tableCustomers","City = 'Edmonton'")

msgbox "the sum is = " & MyVaraable

Or you can go:

msgbox "the sum of payments for customers in Edmonton is " & _
dsum("PayAmount","tableCustomers","City = 'Edmonton'")

if you use no conditions, then all row are summed:

dsum("PayAmount","tableCustomers")
 
DSum doesn't appear to work with a recordset, e.g.
x=dsum("[fldname]",rsvariable) gives an error because the
domain isn't a stirng, but x=dsum
("[fldname]","rsvariable") fails because there is no such
domain. Is there a function (not a sql statement) for
summary operations on a recordset?
 
No, there isn't.

You'll either have to save the recordset as a temporary table and work on
that table, or create a second recordset with the same WHERE clause to
summarize the original data.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



[email protected] said:
DSum doesn't appear to work with a recordset, e.g.
x=dsum("[fldname]",rsvariable) gives an error because the
domain isn't a stirng, but x=dsum
("[fldname]","rsvariable") fails because there is no such
domain. Is there a function (not a sql statement) for
summary operations on a recordset?

-----Original Message-----
Sure, you can use DSUM


MyVaraible = dsum("PayAmount","tableCustomers","City = 'Edmonton'")

msgbox "the sum is = " & MyVaraable

Or you can go:

msgbox "the sum of payments for customers in Edmonton is " & _
dsum("PayAmount","tableCustomers","City = 'Edmonton'")

if you use no conditions, then all row are summed:

dsum("PayAmount","tableCustomers")


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.
Click to expand...
Click to expand...
 
Back
Top