RE; DSum within a Subform problem

[STEVEGARDNER]

Hi,

I am having trouble trying to work out the correct formula to get a DSum working on my subform - "PObyVendor subform"

I have a table called "Purchase Orders" and the field I am trying to sum is called [Total].

Entering the expression:

=Sum ( "[Total]" , "Purchase Orders" )

Brings back the sum of total for all records, however i want to add a criteria that restricts this by another field [Vendor Number] also in the "Purchase Orders" table.

On the subform [Vendor Number] is filtered by a combo box on the main form for [Vendor Name]. This controls a text box for [Vendor Number] (from a vendor table) on the main form and I have a relationship setup linking [Vendor Number] between the two tables.

I don't need [Vendor Number] on the sub-form (it isn't set too visible), Ideally I am looking for an expression that calculates [Total], from the "Purchase Orders" table, where [Vendor Number] = [Vendor Number] on the Main Form - "PObyVendor"

I've got to the expression:

=Sum ( "[Total]" , "Purchase Orders" , "[Vendor Number] = " & Forms!PObyVendor![Vendor Number] )

I've tried many variations trying to reference either [Vendor Number] from either the sub or the main form but have managed nothing more than a succession of 'name?' or '#error' errors!

Sorry If this is all a bit long winded, any help would be greatly appreciated!

Steve

 

[MStef]

I think you need something like "DemoSubNumA2000.mdb" (attachmnet, zip).
Open MainForm, and look at "Sum" in the subform.

 

[STEVEGARDNER]

Yeah, that worked thanks... guess i was trying to over complicate things with a DSum?

Steve