Solved

# Sum in datasheet subform with dlookup fields

Posted on 2006-11-08
997 Views
I have a datasheet subform with some controls that have a calculated value utilizing the dlookup function.  However, as I discovered, I can not simply sum this column up by placing a sum function in the form footer with the appropriate dlookup formulas in it.  Therefore, I need to find a solution as to how to add up such fields so that I can stop banging my head against my keyboard.

0
Question by:bjmatush
• 10
• 5

LVL 61

Expert Comment

Something = DSum("YourField","YourTable","YourCriteria")
0

Author Comment

I tried that...but no go.  I checked that each line is performing the calculations correctly, but the dlookup in the sum field interferes with things.  From what I can tell in the example that I am working with (three lines of data in the datasheet), the dlookup in the sum is simply adding the last line of the datasheet three times, rather than adding each line.

0

LVL 61

Expert Comment

0

Author Comment

I was mistaken...I am only using SUM...not DSum...I have not used DSum before and not too sure what the difference is.  I did just try it now to put it in and it gave me an error stating, "The expression you entered has a function containing the wrong number of arguments".

Here is my original SUM formula:

=Sum((DLookUp("ProductCost","tblProducts","tblProducts!ProductName=Ingredient")/DLookUp("ProductKg","tblProducts","tblProducts!ProductName=Ingredient"))*DLookUp("Conversion","tblUnits","Unit = '" & [Unit] & "'")*[Amount])

It is taking the cost of a product (ingredient), divided by the total weight (to give \$ per kg)...
Then multiplying the number amount (any weight unit), multiplied by conversion factor (to get everything into kilograms).

0

LVL 61

Expert Comment

DSum is kind of like Dlookup, in that it finds values in a given table meeting a specified creiteria.  The difference is that DSum adds them up.  Seeing your original Sum, lets try this.

= (DSum("ProductCost","tblProducts","tblProducts!ProductName=Ingredient")/DLookUp("ProductKg","tblProducts","tblProducts!ProductName=Ingredient")) * DLookUp("Conversion","tblUnits","Unit = '" & [Unit] & "'")*[Amount])
0

LVL 61

Expert Comment

My attempt at math there is factoring out your ProductKg so that you have:

(Total ProductCost)/ProductKg   * Conversion

If you need to divide by the Sum of  ProductKG, change that dlookup to a DSum as well.
0

Author Comment

I gave that a try and what it does is do the "sum" on the current record I click on in the datasheet.  In other words, it does not run through all rows - just "adds" the current row and ignores the others.  It will change as I click on each row.

I am wondering if I might need to approach this by opening the recordset and adding things up manually.  I was hoping on not using that approach since I thought if I can do it directly, that it would be better.

0

LVL 61

Expert Comment

I'm running out of Ideas.  One more thing I can think of to try is to modify your datasheet's record source so that it includes your original DLookup statement.

Create a query with SQL something like this:

Select Field1, Field2,etc.., Dlookup(your original dlookup statement) as SomeFieldName
From YourTableOrQuery
^---- This is the table or query that your subform was based on

Then set the recorssourceof your datasheet subform to this new query.  If you do that, you *should* be able to just use a Sum calculation on that DLookup, referring to it by the fieldname you used in your new query:

= Sum([SomeFieldName])
0

Author Comment

That sounds like a great idea...however, I am now having some difficulty with the syntax to get the dlookup working in the query.  Here is what I have:

Cost: DLookUp("ProductCost","tblProducts","tblProducts!ProductName=[Ingredient]")

I am just starting with a simple dlookup just to retrieve the product cost - not yet actually cost per kg which is what I ultimately need.  But once I get the syntax for this, I should be able to get the whole thing working.

In the query, I have the following fields:
Ingredient
Unit
Amount

0

LVL 61

Accepted Solution

mbizup earned 250 total points
Since ingredient is the name of a field, you need to seperate it out of the quotes, so that the value contained in the field, (not the literal text "[Ingredient]") is used.  Try revising your DLookup like this (assuming Ingredient is text -- let me know if it is numeric):

Cost: DLookUp("ProductCost","tblProducts","tblProducts!ProductName='" & [Ingredient] & "'")
0

LVL 61

Expert Comment

Actually, leave out the table prefix as well:

Cost: DLookUp("ProductCost","tblProducts","ProductName='" & [Ingredient] & "'")

0

LVL 61

Expert Comment

That was very fast!  Did it work?
0

LVL 61

Expert Comment

Anyhow, If you need additional help with this let me know.
0

Author Comment

Oh yeaaaaahhhhh!  It works perfectly.  I really did not want to go through manually calculating things from the recordset since I knew it would involve a lot of code to make sure you didn't get division by 0 errors or failures to update when something in the datasheet change...only to have to go through all that code again to calculate it.  I never thought of putting the dlookup in the query in the first place - and I could not create all my links in the query because it involved 3 tables and then you were not able to edit the datasheet (i.e. add any data into it).

Eloquent solution - thank you!.

0

LVL 61

Expert Comment

0

## Featured Post

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…