Using DSUM Function in a Form 1

[storebay]

I have a form that is based on a payments table. The payments table is linked to a Sales Table. The Sales Table records when a sale is made and an invoice no. is recorded. Everytime the customer makes a payment it is recorded in the payments table (which is linked to the sales table based on invoice no). Now a customer may pay in parts or in one lump sum. What I want to do in the form is create an unbound field that uses DSUM to sum up the amount of payments made on each invoice no.

So for each invoice number in the payments table it sums up all the records with that invoice number and displays the total amount paid on that invoice in this unbound field. However when I try to do it I get #Name? in the field. I think that maybe I am using the DSUM wrong. Can someone break it down for me please? Thanks a lot in advance. Awaiting your replies.

 

[WaltLukeIII]

I have repeatedly tried to use Sum and DSum in fields on Forms and have had no luck. The easiest way to do this is make a query or table that gives the totals then either combine this one with the previous table in a new query and bind the form to this new query or have a function that returns the sum total of each persons payments.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[storebay]

WalkLukeIII: Thanks for your advice. Could you tell me how to do what you recommend? I would be greatly appreciative. Thanks in advance. Awaiting your reply

 

[WaltLukeIII]

Make a new query using something like this

SELECT [table Name].[Column Name], Sum([table Name].[Column Name]) AS [Column Name in Query] From [table Name] GROUP BY [table Name].[Column Name]

This will give you a total field in one query.

The next step is to combine this query with the first table

SELECT [table Name].[Column Name], ...(repeat as many times as needed) FROM [table Name], ...(repeat for all tables used)

This should give you all of the data in the table. You put this code in the SQL view of each query. replacing "table Name" with the name of your tables and "Column Name" with the Name of the Fields from the tables that you use.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[Terpsfan]

DSUM functions are actually easy to use, I make a lot of use of them on forms. The syntax is as follows:
DSum("fieldname","tablename" or "queryname","[fieldname1]=[Forms]![formname]![controlname]
where fieldname is the field you are summing, tablename or queryname is the recordset the field resides in, fieldname1 is the name of the primary key field you are searching within the recordset and [Forms]![formname![controlname] is the fully qualified reference to the form the control is summing which has the primary key. If you need further help, let me know.