DLookUp Sum Function on Cont. Frms

[stevemccauley]

I have a continuous subform that uses the DLookUp function in each of its records. In the footer of the form I would like to have the sum of these records. I used this formula, but I don't know if DLookUp can be combined with the SUM function:

=Sum(DLookUp("[FixCost]","List_Ports","[Port]=Forms!A_Main!B_Port.Form!Port"))

What could be wrong?

 

[WayneRyan]

Steve,

Try the DSum function instead.

Wayne

 

[stevemccauley]

I tried the DSum function, but its only adding up the total of one record. I need it to add up all the records being displayed. I also tried this:

=Sum(DSum("[FixCost]","List_Ports","[Port]=Forms!A_Main!B_Port.Form!Port"))

I get a total of something, but it doesn't equal what it should. What shouls I try?

 

[WayneRyan]

Steve,

I'm a little confused here ...
The DSUM function in your last post will return the sum of
the [FixCost] field for records where [Port] equals
Forms!A_Main!B_Port.Form!Port. There is no need to sum
it as it is already a sum.

The control Forms!A_Main!B_Port.Form!Port seems odd.

Wayne

 

[stevemccauley]

Wayne,

I know I wasn't explaining it well. Here's an example of what I'm trying to do:

Main Form

Continuous SubForm

Record1: Value1(DLookUp function)
Record2: Value2(DLookUp function)
Record3: Value3(DLookUp function)

Footer: =Sum(DSum("[FixCost]","List_Ports","[Port]=Forms!MainForm!SubForm.Form!Port"))

Is this the expression I should be entering in the footer? Because when I just use the DSum alone it only calculates the current record.

Did I give you enough info?

Steve

 

[Pat Hartman]

I too am confused. You should not be using DLookup() in this manner. It is extremely inefficient. Use a join to the List_Ports table to obtain the FixCost amount. The DLookup() method requires Jet to do the lookup for EVERY record. However, if you use a join, Jet will optimize the process and only read the List_ports table once for each Port.

Once you have got the query sorted out, add a control to the subreport's FOOTER. You can then use the Sum() function to sum the FixCost.

=Sum(FixCost)

PS, if you have trouble with the Form not recoginzing the new column in the query, simply delete the query name from the form's controlsource, save the form, and then put the querydef name back. This forces the Form to refresh its fields collection.